SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

A DMV a Day – Day 20

The DMV for Day 20 is sys.dm_exec_cached_plans which is described by BOL as:

Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

This particular DMV works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Use Counts and # of plans for compiled plans
SELECT objtype, usecounts, COUNT(*) AS [no_of_plans] 
FROM sys.dm_exec_cached_plans 
WHERE cacheobjtype = 'Compiled Plan' 
GROUP BY objtype, usecounts
ORDER BY objtype, usecounts;

This query gives you the use counts for each compiled plan in the plan cache. It is broken out by the type of plan. You would want to pay attention to ad-hoc plans where the use count was 1. Looking at the numbers and use counts of your other types of cached plans (such as proc and prepared) can help you better understand your workload. You may be surprised to find out that you get more ad-hoc queries than you expect.


Posted by Anonymous on 20 April 2010

Pingback from  Dew Drop – April 20, 2010 | Alvin Ashcraft's Morning Dew

Posted by Jason Brimhall on 20 April 2010

Thanks Glenn.

Posted by Anonymous on 20 April 2010

Pingback from  How To List Your Used Car | Rebuilder Cars

Leave a Comment

Please register or log in to leave a comment.