Query optimization essentials

The Query Processor is also the component inside the SQL Server Database Engine that is responsible for query optimization. This is the second stage of query processing and its goal is to produce a query plan that can then be cached for all subsequent uses of the same query. In this section, we will focus on the highlighted sections of the following diagram, which handle query optimization:

SQL Server uses cost-based optimization, which means that the Query Optimizer is driven mostly by estimations of the required cost to access and transform data (such as joins and aggregations) that will produce the intended result set. The purpose of the optimization process is to reasonably minimize the I/O, memory, and compute resources needed to execute a query in the fastest way possible. But it is also a time-bound process and can time out. This means that the Query Optimizer may not iterate through all the possible optimization permutations of a given T-SQL statement, but rather stops itself after finding an estimated good enough compromise between low resource usage and faster execution times.

For this, the Query Optimizer takes several inputs to later produce what is called a query execution plan. These inputs are as follows:

• The incoming T-SQL statement, including any input parameters
• The loaded metadata

As part of the optimization process, SQL Server also uses internal transformation rules and some heuristics to narrow the optimization space, that is, to narrow the number of transformation rules that can be applied to the incoming T-SQL statement.

And so, the SQL Server Query Optimizer may transform the T-SQL statement as written by a developer before it is allowed to execute. This is because T-SQL is a declarative language: a developer declares what is intended, but SQL Server determines how to carry out the declared intent.

The Query Optimizer will consider numerous strategies to search for an efficient execution plan:

• Logical operator precedence: When a complex expression has multiple operators, operator precedence determines the sequence in which the operations are performed. For example, in a query that uses comparison and arithmetic operators, the arithmetic operators are handled before the comparison operators.
• Index selection: Are there indexes to cover the whole or parts of the query? This is done based on which search and join predicates are used, and which columns are required for the query output.
• Logical join reordering: The order in which tables are actually joined may not be the same order as they are written in the T-SQL statement itself. SQL Server uses heuristics as well as statistics to narrow the number of possible join permutations to test, and then estimates which join order results in early filtering of rows and less resource usage. For example, depending on how a query that joins six tables is written, possible join reordering permutations range from roughly 700 to over 30,000.
• Partitioning: Is data partitioned? If so, and depending on the predicate, can SQL Server avoid accessing some partitions that are not relevant for the query?
• Parallelism: Is it estimated that execution will be more efficient if multiple CPUs are used?
• Whether to expand views: Is it better to use an indexed view, or to expand and inline the view definition to account for the base tables?
• Join elimination: Are two tables being joined in a way that the number of rows resulting from that join is zero based on existing constraints? If so, the join may not even be executed.
• Sub-query elimination: This follows the same principle as join elimination. Was it estimated that the correlated or non-correlated sub-query will produce zero rows? If so, the sub-query may not even be executed.
• Constraint simplification: Is there an active constraint that prevents any rows from being generated? For example, does a column have a non-nullable constraint, but the query predicate searches for null values in that column? If so, that part of the query may not even be executed.
• Halloween protection: Is this an update plan? If so, is there a need to add a blocking operator?

For the Query Optimizer to do its job efficiently in the shortest amount of time possible, data professionals need to do their part, which can be distilled into the following three principles:

• Design for performance: Ensure that our tables were designed with purposeful use of the appropriate data types and lengths, that our most-used predicates are covered by indexes, and that the engine is allowed to identify and create the required statistical information.
• Write simple T-SQL queries: Be purposeful with the number of joined tables, with how the joins are expressed, with the number of columns needed for the result set, how parameters and variables are declared, and which data transformations are used. Complexity comes at a cost and it may be a wise strategy to break down long T-SQL statements into smaller parts that create intermediate result sets.
• Maintain our database health: From a performance standpoint alone, ensure that index maintenance and statistics updates are done regularly.

At this point, it starts to become clear that how we write a query is fundamental to achieving good performance. But it is equally important to make sure the Query Optimizer is given a chance to do its job to produce an efficient query plan. That job is dependent on having metadata available that accurately portrays data distribution in base tables and indexes. In the Chapter 5, Writing Elegant T-SQL Queries, we will further distill what data professionals need to know to write efficient T-SQL that performs well.

Also, in Chapter 3, Mechanics of the Query Optimizer we will cover the Query Optimizer and the estimation process in greater detail. Understanding how SQL Server optimizes a query and what the process looks like is a fundamental step toward troubleshooting query performance—a task that any data professional will do at some point in their career.