Tuesday, January 23, 2007

4.0 Useful Queries on DMV’s to understand Plan Cache Behavior

by sangeethashekar via SQL Programmability & API Development Team Blog on 1/23/2007 10:00:00 AM

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

1. To view the cached plans use the query sys.dm_exec_cached plans and sys.dm_exec_sql_text. The query below gives the sql text of the query, number of times the query has be executed (or reused), cacheobjtype (Compiled Plan/Extended Stored

1. Procedure/Parse Tree), objtype (View/Proc/Adhoc), bucketid in the hash table these plans are hashed to, and the plan handle.

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

2. The DMV sys.dm_os_memory_cache_entries has the number of 8KB pages allocated for the plan, the number of disk IO's associated with this entry, the number of context switches associated with this query, the original and current cost for the entry. Original cost of the entry is an approximation of the number of I/Os incurred, memory, and the context switch count. The current cost of the entry is the actual cost associated with the query. A query is inserted into the cache with a zero current cost. Its current cost is incremented by one on every re-use. The maximum value of the current cost is the original cost of query. Entries with zero current cost will be removed when the plan cache is under memory pressure. Use either query below to get this information:

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

3. To estimate the amount of plan cache memory that is being reused use:

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.

4. To estimate the amount of memory that can be reclaimed after the next round of memory pressure use:

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.

5. The DMV sys.dm_os_memory_cache_clock_hands has information regarding how many clock rounds have been made for each cache store. The query below should return 4 rows, two for each cachestore. Each cachestore has an external and internal clock hand that distinguishes external and internal memory pressure respectively. The column removed_last_round_count indicates the number of entries (plans) removed in the last round, and the removed_all_rounds_count indicates the total number of entries removed.

select * from sys.dm_os_memory_cache_clock_hands

where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'

go

6. The DMV sys.dm_os_memory_cache_counters has information on the single and multi-page allocations made for the SQLCP and OBJCP cachestores. A large number of multi_pages_kb can lead to a performance slowdown without SQL Server 2005 SP2.

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

7. The DMV sys.dm_os_memory_cache_hash_tables has information on the hash bucket length for SQLCP and OBJCP cachestores. A large value for buckets_average_length and a small value for buckets_in_use_count indicate long chains in each hash bucket. Long hash bucket lengths can lead to performance slowdown.

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.

8. If the size of the plan cache is over a few GB use the following queries:

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

9. To take a full dump turn on T2544 and T8004 (trigger a dump when the lazy writer cannot provide enough free buffers) and execute: dbcc stackdump(0)