THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Tamarick Hill

July, the 31 Days of SQL Server DMO’s – Day 6 (sys.dm_exec_cached_plans)


The sys.dm_exec_cached_plans DMV can be used whenever you want to get a quick look at each query plan that is currently being cached without your SQL Server instance. Using this DMV you are able to determine the amount of space that a particular query plan is consuming, how many objects are referencing a particular plan, and how many times a particular plan has been used.

To further illustrate this DMV, lets run a quick query against it and review the results.

SELECT * FROM sys.dm_exec_cached_plans


The bucketid column represents the hash bucket that contains the cached plan. The refcounts column represents the number of cache objects that are referening this particular plan. The usecounts column represent the number of times that this plan has been looked up.

This DMV can also be used with a CROSS APPLY of the sys.dm_exec_sql_text function to return the batch text for these cached plans.

SELECT cp.refcounts, cp.usecounts, cp.size_in_bytes, cp.objtype, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st


If you are doing some detailed analysis of memory consumption or query plan usage, this DMV can prove very beneficial to you.

For more information on this Dynamic Management View, please see the below Books Online link:

Published Saturday, July 6, 2013 11:41 AM by Tamarick Hill



Tamarick Hill said:

Thanks Jason!

July 7, 2013 11:10 AM
New Comments to this post are disabled
Privacy Statement