THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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

image

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

image

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:

http://msdn.microsoft.com/en-us/library/ms187404.aspx

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Tamarick Hill said:

Thanks Jason!

July 7, 2013 11:10 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement