Database Reference
In-Depth Information
Summary
SQL Server's cost-based query optimizer decides upon an effective execution plan not based on the exact syntax of
the query but by evaluating the cost of executing the query using different processing strategies. The cost evaluation
of using different processing strategies is done in multiple optimization phases to avoid spending too much time
optimizing a query. Then, the execution plans are cached to save the cost of execution plan generation when the
same queries are reexecuted. To improve the reusability of cached plans, SQL Server supports different techniques for
execution plan reuse when the queries are rerun with different values for the variable parts.
Using stored procedures is usually the best technique to improve execution plan reusability. SQL Server
generates a parameterized execution plan for the stored procedures so that the existing plan can be reused when the
stored procedure is rerun with the same or different parameter values. However, if the existing execution plan for a
stored procedure is invalidated, the plan can't be reused without a recompilation, decreasing the effectiveness of plan
cache reusability.
In the next chapter, I will discuss how to troubleshoot and resolve bad parameter sniffing.
