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. |
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. |
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.
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
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.
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
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.