The following queries can be used to understand plan cache behavior. It should be noted that for systems with large plan caches, selecting all rows and joining with sys.dm_exec_sql_text is not recommended. To find the size of the plan cache use:
select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
go
select top 1000 st.text, cp.cacheobjtype, cp.objtype, cp.refcounts, cp.usecounts, cp.size_in_bytes, cp.bucketid, cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where cp.cacheobjtype = 'Compiled Plan'
and cp.objtype = 'Prepared'
order by cp.usecounts desc
go
select top 1000 st.text, cp.cacheobjtype, cp.objtype, cp.refcounts, cp.usecounts, cp.size_in_bytes, cp.bucketid, cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where cp.cacheobjtype = 'Compiled Plan'
and cp.objtype = 'Adhoc'
order by cp.usecounts desc
go
Parameterization of queries gives a significant performance benefit. Parameterized queries have objtype 'Prepared'. Prepared queries typically have large usecounts and are greater in size than the corresponding adhoc shell queries (less than 50K for adhoc shell queries). Plans for stored procedures also have a high degree of reuse. In some workloads, there is reuse of adhoc queries with the exact same parameter values. In such cases caching of the shell query proves gives better throughput.
Sorting the data on usecounts gives the information regarding the degree of reuse of queries. The query below sorts the cached plans on the plan size. This query can be used to identify large plans. Caching several un-parameterized adhoc queries with large plan size and with no reuse will lead to plan cache bloating. This causes the plan cache to be under constant memory pressure and gives suboptimal performance results. It is therefore important to try to parameterize queries.
select top 1000 st.text, cp.cacheobjtype, cp.objtype, cp.refcounts,
cp.usecounts, cp.size_in_bytes, cp.bucketid, cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where cp.cacheobjtype = 'Compiled Plan'
and (cp.objtype = 'Adhoc' or cp.objtype = 'Prepared')
order by cp.objtype desc, cp.size_in_bytes desc
go
Select top 1000 st.text, cp.objtype, cp.refcounts, cp.usecounts, cp.size_in_bytes,
ce.disk_ios_count, ce.context_switches_count,
ce.pages_allocated_count, ce.original_cost, ce.current_cost
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
join sys.dm_os_memory_cache_entries ce
on cp.memory_object_address = ce.memory_object_address
where cp.cacheobjtype = 'Compiled Plan'
and (cp.objtype = 'Adhoc' or cp.objtype = 'Prepared')
order by cp.objtype desc, cp.usecounts desc
go
select st.text, cp.objtype, cp.refcounts, cp.usecounts, cp.size_in_bytes,
ce.disk_ios_count, ce.context_switches_count,
ce.pages_allocated_count, ce.original_cost, ce.current_cost
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
join sys.dm_os_memory_cache_entries ce
on cp.memory_object_address = ce.memory_object_address
where cp.cacheobjtype = 'Compiled Plan'
and ce.type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')
order by cp.objtype desc, cp.usecounts desc
go
select sum(size_in_bytes)/1000 as total_size_in_KB,
count(size_in_bytes) as number_of_plans,
((sum(size_in_bytes)/1000) / (count(size_in_bytes))) as avg_size_in_KB,
cacheobjtype, usecounts
from sys.dm_exec_cached_plans
group by usecounts, cacheobjtype
order by usecounts asc
go
The total_size_in_KB with usecounts > 1 represents the plan cache memory that is being reused.
select ce.type, ce.current_cost,
sum(cp.size_in_bytes) as total_size_in_bytes
from sys.dm_exec_cached_plans cp
join sys.dm_os_memory_cache_entries ce
on cp.memory_object_address=ce.memory_object_address
where ce.type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP') and
ce.current_cost = 0
group by ce.type, ce.current_cost
go
The total_size_in_bytes with current_cost = 0 is the memory that will available after the next round of memory pressure.
select * from sys.dm_os_memory_cache_clock_hands
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
go
select *, name, type, single_pages_kb, multi_pages_kb,
single_pages_in_use_kb, multi_pages_in_use_kb
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
go
Alternatively, query sys.dm_os_memory_clerks to get the memory allocation information along with the memory_node_id:
select type, name, memory_node_id, single_pages_kb, multi_pages_kb
from sys.dm_os_memory_clerks
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
go
select name, type, buckets_count, buckets_in_use_count,
buckets_min_length, buckets_max_length, buckets_avg_length
from sys.dm_os_memory_cache_hash_tables
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
go
Note that by removing the where clause in queries 5 though 7 we can get information for
other cachestores also.
To get a count of the number of compiled plans use:
select count(*) from sys.dm_Exec_Cached_plans
where cacheobjtype = 'Compiled Plan'
To get a count of the number of adhoc query plans use:
select count(*) from sys.dm_Exec_Cached_plans
where cacheobjtype = 'Compiled Plan'
and objtype = 'Adhoc'
To get a count of the number of prepared query plans use:
select count(*) from sys.dm_Exec_Cached_plans
where cacheobjtype = 'Compiled Plan'
and objtype = 'Prepared'
For the number of prepared query plans with a given usecount use:
select usecounts, count(*) as no_of_plans
from sys.dm_Exec_Cached_plans
where cacheobjtype = 'Compiled Plan'
and objtype = 'Prepared'
group by usecounts
For the number of adhoc query plans with a given usecount use:
select usecounts, count(*) as no_of_plans
from sys.dm_Exec_Cached_plans
where cacheobjtype = 'Compiled Plan'
and objtype = 'Adhoc'
group by usecounts
For the top 1000 adhoc compiled plans with usecount of 1 use:
select top(1000) * from sys.dm_Exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where cacheobjtype = 'Compiled Plan'
and objtype = 'Adhoc' and usecounts = 1
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.