Query Processing and Execution - SQL Server 2012 Internals and Troubleshooting - page 120

Use the following DMV to look for heavily used buckets:

select bucketid, count(*) as entries_in_bucket
from sys.dm_exec_cached_plans
group by bucketid
order by 2 desc

You can look up the specii c plans in that bucket using this query:

select *
from sys.dm_exec_cached_plans
where bucketid = 236

If the plans you i nd within the same bucket are all variations on the same query, then try to get
better plan reuse through parameterization. If the queries are already quite different, and there is
no commonality that would allow parameterization, then the solution is to rewrite the queries to be
dramatically different, enabling them to be stored in emptier buckets.

Another approach is to query sys.dm_exec_query _stats, grouping on query_plan_hash to i nd
queries with the same query plan hash using the T-SQL listed here:

select query_plan_hash,count(*) as occurrences
from sys.dm_exec_query_stats
group by query_plan_hash
having count(*) > 1

Four different kinds of objects are stored in the plan cache. Although not all of them are of equal
interest, each is briel y described here:
➤

Algebrizer trees are the output of the algebrizer, although only the algebrizer trees for
views, defaults, and constraints are cached.

➤

Compiled plans are the objects you will be most interested in. This is where the query plan is
cached.

➤

Cursor execution contexts are used to track the execution state when a cursor is executing,
and are similar to the next item.

➤

Execution contexts track the context of an individual compiled plan.

The i rst DMV to look at in the procedure cache is sys . dm _ exec _ cached _ plans . The following
query gathers some statistics on the type of objects exposed through this DMV (note that this
doesn't include execution contexts, which are covered next):

select cacheobjtype, objtype, COUNT (*)
from sys.dm_exec_cached_plans
group by cacheobjtype, objtype
order by cacheobjtype, objtype

Running the preceding on my laptop resulted in the following output; your results will vary accord-
ing to what was loaded into your procedure cache:

CACHEOBJTYPE OBJTYPE (NO COLUMN NAME)
Compiled Plan Adhoc 43