Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

MSSQLFUN

I, Rohit Garg, am working as Consultant in IT Company. I am having an around 5 years of experience in MSSQL server & other Microsoft technologies. I am working as DBA in Microsoft SQL Server from last 5 years in e-Commerce, Telecom, Finance domain. In this tenure, I got a chance of working as Database administrator, Developer and trainer on SQL server 2000 to SQL Server 2012. I am holding Master’s degree in Computer Science along with certification in SQL Server & .Net. I like to learn new things by hand-on experience on regular basis. This journey is so far is delightful & valuable with the addition of wonderful friends.

DMV-5 : Queries runing are adhoc or proc , single or multi use ?……..sys.dm_exec_cached_plans

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 execu­tion. 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/)


Comments

Leave a comment on the original post [mssqlfun.com, opens in a new window]

Loading comments...