Database Reference
In-Depth Information
Chapter 15
Execution Plan Cache Behavior
Once all the processing necessary to generate an execution plan has been completed, it would be crazy for SQL
Server to throw away that work and do it all again each time a query gets called. Instead, it saves the plans created in
a memory space on the server called the
plan cache
. This chapter is going to walk through how you can monitor the
plan cache in order to see how SQL Server reuses execution plans.
In this chapter, I cover the following topics:
•
How to analyze execution plan caching
•
Query plan hash and query hash as mechanisms for identifying queries to tune
•
Execution plans gone wrong and parameter sniffing
•
Ways to improve the reusability of execution plan caching
Analyzing the Execution Plan Cache
You can obtain a lot of information about the execution plans in the procedure cache by accessing various dynamic
management objects. The initial DMO for working with execution plans is
sys.dm_exec_cached_plans
.
SELECT *
FROM sys.dm_exec_cached_plans;
Table
15-1
shows some of the useful information provided by
sys.dmexeccachedplans
(this is easier to read in
Grid view).
