Abstract
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
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Preview
Unable to display preview. Download preview PDF.
Rights and permissions
Copyright information
© 2012 Grant Fritchey
About this chapter
Cite this chapter
Fritchey, G. (2012). Query Recompilation. In: SQL Server 2012 Query Performance Tuning. Apress, Berkeley, CA. https://doi.org/10.1007/978-1-4302-4204-8_10
Download citation
DOI: https://doi.org/10.1007/978-1-4302-4204-8_10
Publisher Name: Apress, Berkeley, CA
Print ISBN: 978-1-4302-4203-1
Online ISBN: 978-1-4302-4204-8
eBook Packages: Professional and Applied ComputingProfessional and Applied Computing (R0)Apress Access Books