Database Reference
In-Depth Information
This creates a plan guide based on the execution plan as it currently exists in the cache. To be sure this
works, clear the cache again. That way, the query has to generate a new plan. Rerun the query, and observe the
execution plan. It will be the same as that displayed in Figure
17-20
because of the plan guide created using
sp_create_plan_guide_from_handle
.
Plan guides are useful mechanisms for controlling the behavior of SQL queries and stored procedures, but you
should use them only when you have a thorough understanding of the execution plan, the data, and the structure of
your system.
Summary
As you learned in this chapter, query recompilation can both benefit and hurt performance. Recompilations that
generate better plans improve the performance of the stored procedure. However, recompilations that regenerate
the same plan consume extra CPU cycles without any improvement in processing strategy. Therefore, you should
look closely at recompilations to determine their usefulness. You can use Extended Events to identify which stored
procedure statement caused the recompilation, and you can determine the cause from the
recompile_clause
data
column value in the Extended Events output. Once you determine the cause of the recompilation, you can apply
different techniques to avoid the unnecessary recompilations.
Up until now, you have seen how to benefit from proper indexing and plan caching. However, the performance
benefit of these techniques depends on the way the queries are designed. The cost-based optimizer of SQL Server
takes care of many of the query design issues. However, you should adopt a number of best practices while designing
queries. In the next chapter, I will cover some of the common query design issues that affect performance.
