Optimization Preparation

There is a complex set of inputs and outputs that don’t really need to be explored in order to understand what’s going on. However, just so we’re clear, Figure 2-1 shows the process, the inputs, and outputs:

A flowchart exhibits the paths to the storage engine from a yes or no question for D M L statement in the algebrizer from the T S Q L statement.

Let’s take a look at the two steps that take place prior to the optimization process itself.

Binding

The parse tree is now used to identify all the objects that make up the query. This list of objects includes tables, columns, indexes, and more. This process is called binding. All the data types being processed are identified. Any kinds of aggregation through GROUP BY are identified. All these objects and processes are put together into another internal structure called a query processor tree.

The algebrizer will also handle implicit data conversions by adding steps to the processor tree. You may also see syntax optimizations occur where the code you passed to SQL Server actually gets changed. If you take the code from Listing 2-2 and capture an execution plan (covered in Chapter 4), you can actually see the changes in syntax.

SELECT soh.AccountNumber,

       soh.OrderDate,

       soh.PurchaseOrderNumber,

       soh.SalesOrderNumber

FROM Sales.SalesOrderHeader AS soh

WHERE soh.SalesOrderID

BETWEEN 62500 AND 62550;

Listing 2-2

The original code before binding

Figure 2-2 shows the change in syntax.

A pop-up window labeled Statement exhibits a text box with syntax changes that start with SELECT followed by various variables such as account number.

Two very distinct changes have been made. First, the BETWEEN statement has been replaced with “>=” and “<=”. Second, a process called simple parameterization has been implemented through the @1 and @2 you see in the figure. We’ll discuss more about parameterization later in this chapter. You can see further evidence of the binding process if we look at the execution plan generated in Figure 2-3.

A flow diagram exhibits, from right to left, clustered index, compute scalar, computer scalar, and select with a brightly colored warning sign.

The exclamation point visible on the very first operator on the left, labeled “SELECT”, is an indication of a warning. Looking at the properties for that operator, you’ll find that the SalesOrderID is actually going through a data type conversion:

Type conversion in expression (CONVERT(nvarchar(23),[soh].[SalesOrderID],0)) may affect "CardinalityEstimate" in query plan choice

This example illustrates several points in addition to syntax-based optimization. First, warnings can sometimes be unclear. In this case, the warning is not actually from the SalesOrderID column as referenced in the WHERE clause of the query. Instead, it’s from a calculated column, SalesOrderNumber. That calculation is converting the SalesOrderID, an integer, into a string and adding other information to it. In the way this calculation is being done, the optimizer recognizes that were this to be used as filtering criteria in a WHERE clause, or JOIN criteria, or even a HAVING clause, it would affect the ability to use statistics and indexes to get good performance. In this case though, the query doesn’t reference the column in any of the filtering criteria, so we can safely ignore the warning. I like this example because it shows just how realistic AdventureWorks really is. A lot of this kind of issue creeps into real-world databases.

Unlike Data Manipulation Language (DML) queries that we’ve seen so far, Data Definition Language (DDL) queries, such as CREATE TABLE, do not get execution plans. There is only one way to run a CREATE VIEW query as an example. Therefore, most DDL doesn’t go through the optimization process. The exception is CREATE INDEX DDL queries. The optimizer can take advantage of existing indexes to create another index more efficiently. So while you’ll never see a CREATE PROCEDURE execution plan, you may see one when creating an index.

Once all the binding operations are complete, another internal structure is created, called the query processor tree. This is where optimization starts.

Simplification

The first step in optimization is a process called Simplification. Here the optimizer ensures that all the objects that you reference in your query are actually in use. Statistics and row counts begin to be gathered and used. This step looks at the constraints such as foreign keys. When all this is taken together, you can see the optimizer actually change what is being processed. For example, you may write a query with a four-table join. However, only two tables are referenced in the SELECT list and the WHERE clause. Using the constraints, the optimizer can tell that it doesn’t need the other two tables to satisfy the query and will therefore eliminate them from further processing, simplifying what is being queried—hence the name of this step.

Trivial Plan Match

When a query is extremely simplistic, it can match a pattern for which there are no options, but only one possible execution plan. That situation is referred to as a Trivial Plan. The simplest example is a table with no clustered index, referred to as a heap, and no other indexes. The only way this table can be accessed is through a table scan. Rather than even attempting to build an execution plan, the optimizer looks for the query pattern that matches existing Trivial Plans. When a pattern match is found, optimization, an expensive operation, is skipped entirely. Instead, the Trivial Plan is used to satisfy the query. As stated before, you’ll only see Trivial Plans in extremely simple queries. You can see that a plan is marked Trivial in the properties of the execution plan (discussed in Chapter 4).

Optimization Phases

The optimizer can short-circuit these searches, heading straight to Quick Plan or Full Optimization, skipping Transaction, depending on the query, its complexity, and the supporting objects such as indexes and constraints. These phases can each decide on how to perform a particular JOIN operation or access the data through scans or seeks.

The principal driver for most of the decisions within the optimizer are row counts. Row counts generally come from the statistics of the columns in the filtering criteria such as ON, HAVING, and WHERE predicates. With the statistics in hand, the optimizer begins to make choices and calculations based on how much estimated CPU, memory, and I/O are necessary to satisfy the query. These estimates are added up to a total cost for the execution of a query. That cost is not a measure based on your system. Rather it’s an arbitrary calculation made by the optimizer and is purely a mathematical construct.

The optimizer will make a calculation on the number of iterations it needs to run through, attempting different plans, with different strategies. At the same time, it calculates what is likely to be the least cost plan. When it finds the plan that meets all the calculated requirements, it will stop optimization, even if a better plan could be found. This is known as Early Termination of Optimization. You’ll also see the termination of optimization when the optimizer has gone through the calculated number of iterations. At that point, it will end optimization through what is referred to as a Timeout (more on that in Chapter 4).

If a query reaches Search 2, or Full Optimization, it may also be evaluated for conversion from a serial plan to a parallel plan (more on that later in the chapter).

If we capture the execution plan for this query (again, covered in Chapter 4), we can see some of the behavior of the optimizer. This plan is hard to read within this context, but I don’t want you to try to understand it all at this point. The plan is shown in Figure 2-5.

A flow diagram of, from right to left, an index seek, nested loops with linking compute scalars and distinct clustered index seeks, and select.

Don’t try to read that plan. It’s much too small. Instead of reading the plan now, we’re going to focus on one spot, the operator on the far left side of the plan. In this case, since it’s a SELECT query, the operator is labeled SELECT. The properties of this operator are all of the information about the execution plan itself. These include information about the optimization process. We can see a subset of those properties in Figure 2-6.

A table has 2 columns and 9 rows for m i s c. The leftmost row entries have pieces of information such as cached plan size and compile time.

That’s just a sample of the amount of information gathered here for how the optimizer works to arrive at an execution plan. We can also look to this data to see things like the Optimization Level and the Reason for Early Termination, the top and bottom properties shown in Figure 2-7.

A table has 2 columns and 6 rows. The leftmost row has entries such as optimizer hardware dependent properties and optimizer stats usage with a plus symbol on the left of the table.

One point worth noting here, when we see the property Optimization Level and a value of “Full” here, it doesn’t necessarily mean that this plan went through Search 2. It only means that it’s not a Trivial Plan.

A partial set of the results from the query are visible in Figure 2-8.

A table has unlabeled, counter, occurrence, and value columns and 1 to 15 labeled rows. The optimizations entry in the first row under counter is highlighted.

You can see that the system I’ve run this on has done very few complex optimizations. We can tell that because there have been no Search 2 operations. You can see the other operations that have been done and the estimated time needed to accomplish them.

This information is only of limited use, but it’s good to know where to go to get it in the event that you need to understand some of the processes that the optimizer is going through and the load it’s placing on your systems.

You could also run this code before and after a query in order to see the types of optimizations that occurred on that query. However, you need to do this on an isolated system or you’re likely to see more than just the optimizations for the query you’re investigating.

Aging of the Execution Plan

The plan cache is part of SQL Server’s buffer cache, which also holds data pages. As new execution plans are added to the plan cache, the size of the plan cache keeps growing, affecting the retention of useful data pages in memory. To avoid this, SQL Server dynamically controls the retention of the execution plans in the plan cache, retaining the frequently used execution plans and discarding plans that are not used for a certain period of time.

SQL Server keeps track of the frequency of an execution plan’s reuse by associating an age field to it. When an execution plan is generated, the age field is populated with the cost of generating the plan. A complex query requiring extensive optimization will have an age field value higher than that for a simpler query.

At regular intervals, the current cost of all the execution plans in the plan cache is examined by SQL Server’s lazy writer process (which manages most of the background processes in SQL Server). If an execution plan is not reused for a long time, then the current cost will eventually be reduced to 0. The cheaper the execution plan was to generate, the sooner its cost will be reduced to 0. Once an execution plan’s cost reaches 0, the plan becomes a candidate for removal from memory. SQL Server removes all plans with a cost of 0 from the plan cache when memory pressure increases to such an extent that there is no longer enough free memory to serve new requests. However, if a system has enough memory and free memory pages are available to serve new requests, execution plans with a cost of 0 can remain in the plan cache for a long time so that they can be reused later, if required.

As well as changing the costs downward, execution plans can also find their costs increased to the max cost of generating the plan every time the plan is reused (or to the current cost of the plan for ad hoc plans). For example, suppose you have two execution plans with generation costs equal to 100 and 10. Their starting cost values will therefore be 100 and 10, respectively. If both execution plans are reused immediately, their age fields will be set back to that maximum cost. With these cost values, the lazy writer will bring down the cost of the second plan to 0 much earlier than that of the first one, unless the second plan is reused more often. Therefore, even if a costly plan is reused less frequently than a cheaper plan, because of the effect of the initial cost, the costly plan can remain at a nonzero cost value for a longer period of time.