Reuse of Execution Plans

Execution plans remain in the procedure cache for a while. If the same or some other user issues a similar batch, the relational engine first attempts to find a matching execution plan in the procedure cache. If it exists, it will be reused and, therefore, compilation cost will be avoided. If it does not exist, SQL Server parses and compiles a batch.

If SQL Server requires more memory than is available, it might remove some execution plans from memory. There is a sophisticated "aging" algorithm that takes into account how long ago and how many times an execution plan was used. If there is an abundance of memory, it is possible that execution plans will remain in the cache indefinitely.

Execution contexts are also cached and could be reused, but unlike execution plans, they are single-threaded; they can be used by one process at the time. A process first looks for a matching execution plan and when it finds it, it looks for suitable execution context. "Suitable" here means that it is not required that execution context matches exactly.

Note 

A plan does not have to be in the plan cache to he executed. In fact, there are some plans that will never go into plan cache. For example, hatches that contain literals (constants) that are more than 8KB large or hatches that contain hulk insert statements will never he cached. However, a plan has to he cached for a process to find it and reuse it.

Levels of Execution Plans

Execution plans can be based on different types of objects:

• Batch

• Stored procedure

• Query

• Trigger

• Prepared statement

• Ad hoc query

• Statement (!!!)

Note 

Statement-level execution plans and the possibility of their recompilation (I'll discuss recompilation in the following sections) are a SQL Server feature with very important performance implications. In SQL Server 2000 and earlier versions, it was advised to reduce the size of stored procedures that a system will often recompile. This is not so important in SQL Server 2005, which can recompile just the statement that has caused the recompilation, instead of recompiling a whole stored procedure or a hatch.

Caching Levels

The following types of batches are cached separately even when they are created as part of a larger batch:

• Stored procedures

• Autoparameterized queries

• Batches executed using the Exec command

Every level provides additional opportunities for matching and reuse. The only exception occurs when the outer batch is actually a stored procedure and it is executing a nested stored procedure. In this case, the nested stored procedure cannot be reused by another stored procedure that is referencing it.

Reuse of Query Execution Plans

A simple query can be reused only in two scenarios. First, the query text of the second query must be identical to the text of the query described by the execution plan in the cache. Everything has to match—spaces, line breaks, indentation—even case on case-sensitive servers.

The second scenario may occur when the query contains fully qualified database objects to reuse execution plans:

      Select *      from Asset5.dbo.Inventory 

Parameterized Queries

The designers of SQL Server have created two methods to improve the reuse of queries that are not designed as stored procedures:

• Autoparameterization

• The sp_executesql stored procedure

The first of these methods is covered in the following section and the second one in Chapter 15.

Autoparameterization

When a Transact-SQL statement is sent to SQL Server, it attempts to determine whether any of its constants can be replaced with parameters. Subsequent queries that use the same template will reuse the same execution plan.

For example, let's say that SQL Server receives the following ad hoc query:

      SELECT FirstName, LastName, Phone, Fax, Email, OrgUnitld, UserName      FRO M Asset5.dbo.Contact      where ContactId = 3 

It will try to parameterize it in the following manner and create an execution plan:

      SELECT FirstName, LastName, Phone, Fax, Email, OrgUnitld, UserName      FROM Asset5.dbo.Contact      where ContactId = @P1 

After this, all similar queries will reuse the execution plan:

      SELECT FirstName, LastName, Phone, Fax, Email, OrgUnitld, UserName      FROM Asset5.dbo.Contact      where ContactId = 11 

SQL Server applies autoparameterization only when a query's template is "safe"— that is, when the execution plan will not be changed and the performance of SQL Server will not be degraded if parameters are changed.

Note 

SQL Server might decide to create and use a different execution plan even if the query is based on the same field. For example, imagine that you are querying a table with contact information using the Country field. If your company is operating predominantly in North America, SQL Server might carry out a query for Denmark contacts based on the index on the Country field, and a query for USA contacts as a table scan.

SQL Server attempts autoparameterization on Insert, Update, and Delete statements, too. First, I'll review the conditions for autoparameterization in SQL Server 2000, and then for those in SQL Server 2005.

The query must match a set of four templates in order for SQL Server 2000 to attempt autoparameterization:

      Select {*   column-list}      From table      Where column-expression      [Order by column-list] 

      Insert table      Values ({constant   NULL   Default} [, ...n]} 

      Update table      set column-name = constant      where column-expression 

      Delete table      Where column-expression 

Note that column-expression is an expression that involves only column names, constants, the And operator, and the comparison operators: <, >, =, >=, <=, and < >.

SQL Server 2000 is more forgiving about formatting the query when autoparameterization is used, but it still does not allow changes in capitalization or changes in the way an object is qualified.

On SQL Server 2005, Microsoft defines conditions for autoparameterization in the opposite way—by listing cases that will not be considered as candidates for autoparameterization:

• Update and Delete statements that contain a From clause with any of following:

  • More than one table

  • Table-valued variable or function

  • Table or index hints

  • Tablesample clause

  • Full-text table

  • OpenRowSet() function

  • OpenQuery() function

  • OpenDataSource() function

  • OpenXml() function

  • XmlUnnext() function

  • IRowSet() function=

• Update statements with an Order By clause

• Any statement with the Top clause

• WaitFor statements

• Bulk Insert statements

• Insert…Exec statements

• Select statements with an In operator in a Where clause

• Select statements with Distinct

• Select or Update statements with a For Browse clause

• Select statements with query hints with an Option clause

• Select statements with a subquery

• Select statements with a Group By, Having, Compute By, Grouping, Union, Into, or For Update clause

• Select statements with CTE (Common Table Expressions)

• Statements with a Where clause with expressions joined by Or

• Statements with a Where clause with expressions that use a <> comparison with constants

• Statements with a Where clause with expressions that compare two constants

• Update statements with Set statements that reference variables

• Full-text predicates

• Statements in a string submitted through an Exec statement

• If 1,000 or more parameters are identified by autoparameterization

Reuse of Stored Procedure Execution Plans

Stored procedures do not have the limitations associated with ad hoc queries, and that is the main reason stored procedures are reused more often than queries.

The reuse of execution plans is one of the main reasons why the use of stored procedures is a better solution than the use of ad hoc queries. For example, if you execute a query three times, SQL Server will have to parse, recompile, and execute it three times. A stored procedure will most likely be parsed and recompiled only once—just before the first execution.

Note 

Someone might argue that the time needed to compile is insignificant compared with the time needed to execute a query. That is sometimes true. But the SQL Server query engine in later versions compares dozens of new processing techniques in order to select the best one to process the query or stored procedure. Therefore, the time needed to recompile a stored procedure is greater in later versions than it is in earlier versions. Fortunately, the introduction of statement-level compilation in SQL Server 2005 will reduce the amount of time needed to recompile a hatch or a stored procedure to a time needed to recompile a statement that has led to recompilation.

The execution plan with multiple execution contexts is cached in plan cache. The execution plan will be removed from the procedure cache when a process called lazywriter concludes that the execution plan has not been used for a while and that SQL Server needs more memory, or when the execution plan's dependent database objects are changed in any of the following ways:

• The amount of data is significantly changed.

• Indexes are created or dropped.

• Constraints are added or changed.

• Distribution statistics of indexes are changed.

• sp_recompile was explicitly called to recompile the stored procedure or trigger.

I was impressed with the way that lazywriter determines which execution plans are obsolete. Microsoft SQL Server 2000 contains a sophisticated emulation of the aging process. When SQL Server creates an execution plan, it assigns it a compilation cost factor. The value of this factor depends on the expense required to create the execution plan in terms of system resources. For example, a large execution plan might be assigned a compilation cost factor of 8, while a smaller one might be assigned a factor of 2. Each time the execution plan is referenced by a connection, its age is incremented by the value of the compilation cost factor. Thus, if the compilation cost factor of the execution plan is 8, each reference to the execution plan adds 8 to its "age."

SQL Server uses the lazywriter process to decrement the age of the execution plan. The lazywriter process periodically loops through the execution plans in the procedure cache and decrements the age of each execution plan by 1. When the age of an execution plan reaches 0, SQL Server deallocates it, provided that the system is in need of the resources and no connection is currently referencing the execution plan.

SQL Server 2005 works a little differently. A query cost is calculated as the sum of IO cost, CPU cost, and memory cost. Its unit is tick (number of ticks) and the maximum value of a query is 31 ticks.

IO cost is calculated as 1 tick for each 2 IDs, with a limit of 19 ticks. Two context switches of CPU are considered as 1 tick of CPU cost. Its maximum is set to 8 ticks. A query execution that uses up to 16 memory pages (128 KB) has a memory cost of 1 tick. The maximum of memory cost is 4 ticks.

SQL Server 2005 does not use lazywriter to periodically age execution plans. Its behavior is much simpler. When the plan cache reaches 50 percent of the buffer pool size, the next plan access decrements all existing execution plans by 1. When it reaches 75 percent, a dedicated resource monitoring thread is reactivated and it decrements tick counts of all existing execution plans by 1. When the plan is reused, its cost is returned to the original value. When the age of an execution plan reaches 0, SQL Server deallocates it (only if nothing is using it at that moment).

If a dependent database object is deleted, the stored procedure will fail during execution. If it is replaced with a new object (new object identification number) with the same name, the execution plan does not have to be recompiled and will run flawlessly. Naturally, if the structure of the dependent object is changed so that objects that the stored procedure is referencing are not present or not compatible anymore, the stored procedure will fail, resulting in a runtime error.

In some cases, an execution plan cannot be reused:

• When an instance of a stored procedure is executed against one database, the execution plan cannot be reused when another instance of the stored procedure is executed against a different database.

Note 

Ad hoc queries, batches, and dynamic queries from the Exec statement can he reused even in this case.

• Batches with unqualified object names (such as select * from My Table).

Tip 

This is why it is critical to prefix everything with a schema name.

• Batches with constants longer than 8KB.

• Batches with bulk insert statements.

• Batches from CLR cannot reuse execution plans of Transact-SQL batches, and vice versa.

• Notification queries cannot reuse non-notification queries, and vice versa.

• Batches flagged with the For Replication flag cannot reuse batches that are not flagged, and vice versa.

• Stored procedures that were created or executed using the With Recompilation clause.