sys.dm_exec_cached_plans DMV (Dynamic Management View), described by BOL as follows : http://msdn.microsoft.com/en-us/library/ms187404.aspx
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.
sys.dm_exec_cached_plans provide execution related details like no. of queries comes under adhoc or proc section. It help is understanding the query type & performance improvisation by looking into heavily running adhoc queries.
Query 1 : Memory used by cache plan of each database
SELECT
CP.CACHEOBJTYPE,
CP.OBJTYPE,
CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,
SUM(CASE WHEN CP.USECOUNTS <= 1 THEN 1 ELSE 0 END) AS SINGLE_USE_COUNT,
SUM(CASE WHEN CP.USECOUNTS > 1 THEN 1 ELSE 0 END) AS MULTI_USE_COUNT,
AVG(CASE WHEN CP.USECOUNTS > 1 THEN CP.USECOUNTS ELSE NULL END) AS MULTI_USE_AVG_USE_COUNT,
(SUM(CASE WHEN CP.USECOUNTS <= 1 THEN CP.SIZE_IN_BYTES ELSE 0 END) / (1024 * 1024)) AS SINGLE_USE_SIZE_IN_MBYTES,
(SUM(CASE WHEN CP.USECOUNTS > 1 THEN CP.SIZE_IN_BYTES ELSE 0 END) / (1024 * 1024)) AS MULTI_USE_SIZE_IN_MBYTES,
(SUM(CP.SIZE_IN_BYTES) / (1024 * 1024)) AS TOTAL_SIZE_IN_MBYTES
FROM
SYS.DM_EXEC_CACHED_PLANS AS CP
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(CP.PLAN_HANDLE) AS ST
GROUP BY
CP.CACHEOBJTYPE,
CP.OBJTYPE,
CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END
ORDER BY
CP.CACHEOBJTYPE,
CP.OBJTYPE,
CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END
Query 2 : Find single-use, ad-hoc queries
SELECT ST.[TEXT] ,
CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,
CP.SIZE_IN_BYTES
FROM
SYS.DM_EXEC_CACHED_PLANS AS CP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(CP.PLAN_HANDLE) AS ST
WHERE CP.CACHEOBJTYPE = ‘COMPILED PLAN’
AND CP.OBJTYPE = ‘ADHOC’
AND CP.USECOUNTS = 1
ORDER BY CP.SIZE_IN_BYTES DESC
Remarks
1. User required VIEW SERVER STATE permission on the server.
If you liked this post, do like on Facebook at https://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
