1045
Chapter 46: Maximizing Query Plan Reuse
46
Single statement query recompiles simply cause the query to recompile the next time it’s
executes. But stored procedures can recompile midstream.Following are the most common causes of an automatic recompile:■ Updated Statistics. If statistics for data referenced by the query update, SQL Server
blocks execution and recompiles based on the newer statistics. If asynchronous sta-
tistics updates occur, the update occurs in the background. The current query does
not block, but it uses the out-of-date statistics for the current execution.
■ Trivial plans. SQL Server cannot cache execution plans for reuse if the plan has a
single best method for execution. An example is a SELECT * on a table that does not
include a predicate.■ (^) A large change in the number of rows in the base table referenced by the query (if
AUTO_UPDATE of statistics is enabled), even rows inserted by a trigger.
■ (^) Mixing DML statements and DDL statements in a stored procedure can cause a
recompile. For example, creating a temp table, running an update, and then creat-
ing another temp table forces a recompile of the stored procedure following the
second temp table’s creation.
■ (^) Some ALTER DATABASE commands force all plans out of the plan cache for a give
database.
■ (^) A table, view, or index referenced by the query is altered. Therefore, if the stored
procedure batch creates any temp tables, create them all at the beginning of the
stored procedure or batch.
■ Explicit recompile statements, such as using the OPTION(RECOMPILE) statement to
force a recompile of a statement or stored procedure
Beginning with SQL Server 2005, individual statements recompile instead of whole batches.
This means that recompiles are less costly, even if they occur more frequently.
The sp_recompile system stored procedure forces a recompile of any query plan stored
that references that object (a stored procedure, table, or view) the next time the query
executes. You can use the OPTION(RECOMPILE) hint at a statement level to force individual
statements to recompile and forcibly not cache when included with a SQL statement.
You can monitor recompiles using SQL Profi ler using the SQL:StmtRecompile event. You can
also track recompiles using SQL Counters in Perfmon and with Extended Events.
Although plan reuse is typically a good thing, there are situations in which it is detrimental to performance. SQL
Server creates execution plans based on the parameters passed in for the fi rst execution of the parameterized state-
ment. This is known as parameter sniffing. On subsequent executions, the cached plan will be reused regardless of
the parameter values passed, which can cause performance problems if the stored plan is not well suited for the
parameters.
c46.indd 1045c46.indd 1045 7/31/2012 10:22:58 AM7/31/2012 10:22:58 AM
http://www.it-ebooks.info