Database Reference
In-Depth Information
Chapter 17
Query Recompilation
Stored procedures and parameterized queries improve the reusability of an execution plan by explicitly converting
the variable parts of the queries into parameters. This allows execution plans to be reused when the queries are
resubmitted with the same or different values for the variable parts. Since stored procedures are mostly used to
implement complex business rules, a typical stored procedure contains a complex set of SQL statements, making the
price of generating the execution plan of the queries within a stored procedure a bit costly. Therefore, it is usually
beneficial to reuse the existing execution plan of a stored procedure instead of generating a new plan. However,
sometimes the existing plan may not be optimal, or it may not provide the best processing strategy during reuse. SQL
Server resolves this condition by recompiling statements within stored procedures to generate a new execution plan.
This chapter covers the following topics:
•
The benefits and drawbacks of recompilation
•
How to identify the statements causing recompilation
•
How to analyze the causes of recompilations
•
Ways to avoid recompilations when necessary
Benefits and Drawbacks of Recompilation
The recompilation of queries can be both beneficial and harmful. Sometimes, it may be beneficial to consider a
new processing strategy for a query instead of reusing the existing plan, especially if the data distribution in the
table (or the corresponding statistics) has changed or new indexes are added to the table. Recompiles in SQL Server
2014 are at the statement level. This increases the overall number of recompiles that can occur within a procedure,
but it reduces the effects and overhead of recompiles in general. Statement-level recompiles reduce overhead
because they recompile only an individual statement rather than all the statements within a procedure, whereas
recompiles in SQL Server 2000 caused a procedure, in its entirety, to be recompiled over and over. Despite this
smaller footprint for recompiles, it's something to be reduced and controlled as much as is practical for
your situation.
To understand how the recompilation of an existing plan can sometimes be beneficial, assume you need to
retrieve some information from the
Production.WorkOrder
table. The stored procedure may look like this:
IF (SELECT OBJECT_ID('dbo.WorkOrder')
) IS NOT NULL
DROP PROCEDURE dbo.WorkOrder;
GO
CREATE PROCEDURE dbo.WorkOrder
AS
