Execution DMVs

Which queries are in the cache, and how expensive are they? This collection covers sys.dm_exec_query_stats, sys.dm_exec_cached_plans and sys.dm_exec_plan_attributes. The first two queries find every cached instance matching a piece of text; the plan-attributes query exposes what SQL Server knows about each cached plan; the plan-cache summary reports how much memory each cache object type is holding - including how much of it has only ever been used once (prime candidates for eviction); the next one gives per-statement execution counts with average physical/logical reads, writes, CLR time and elapsed time for the last two hours; and the final query ranks statements by average CPU time using query hashes.

/*
sys.dm_exec_query_stats
sys.dm_exec_cached_plans
sys.dm_exec_plan_attributes
*/



-------------------------------
-- dm_exec_query_stats
--
-- Find all instances based on query text from dm_exec_query_stats
-------------------------------  
select * 
from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
where text like '%select * from Person.Address%'


-------------------------------
-- dm_exec_cached_plans
--
-- Find all instances based on query text from dm_exec_cached_plans - tip 1661
-------------------------------  
SELECT usecounts, cacheobjtype, objtype, text, query_plan, ISNULL(DB_NAME(dm_exec_sql_text.dbid),'ResourceDB') AS DatabaseName
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
where text like '%select * from Person.Address%'
ORDER BY usecounts DESC;


-------------------------------
-- dm_exec_plan_attributes
--
-- Query stats and plan attributes - tip 1829
-------------------------------
SELECT SDEQP.query_plan, SDEPA.attribute, SDEPA.value, *
FROM sys.dm_exec_cached_plans SDECP 
   INNER JOIN sys.dm_exec_requests SDER ON SDECP.plan_handle = SDER.plan_handle 
   INNER JOIN sys.dm_exec_sessions SDES ON SDER.session_id = SDES.session_id 
   CROSS APPLY sys.dm_exec_query_plan(SDECP.plan_handle) AS SDEQP 
   CROSS APPLY sys.dm_exec_plan_attributes(SDECP.plan_handle) AS SDEPA 

   
-------------------------------
-- Query stats and plan cache - tip 2527
-------------------------------
WITH CACHE_ALLOC AS
(
SELECT  objtype AS [CacheType]
        ,COUNT_BIG(objtype) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
        , CASE 
      WHEN (Grouping(objtype)=1) THEN count_big(objtype)
      ELSE 0 
      END AS GTOTAL
          FROM sys.dm_exec_cached_plans
GROUP BY objtype 
) 
   SELECT
      [CacheType], [Total Plans],[Total MBs],
      [Avg Use Count],[Total MBs - USE Count 1],
      Cast([Total Plans]*1.0/Sum([Total Plans])OVER() * 100.0 AS DECIMAL(5, 2)) As Cache_Alloc_Pct
      FROM CACHE_ALLOC
      Order by [Total Plans] desc



-------------------------------
-- Query stats and plan cache - tip 1843
-------------------------------
SELECT  
       sdest.dbid  
       ,sdest.[text] AS Batch_Object, 
       SUBSTRING(sdest.[text], (sdeqs.statement_start_offset/2) + 1, 
       ((CASE sdeqs.statement_end_offset 
               WHEN -1 THEN DATALENGTH(sdest.[text]) ELSE sdeqs.statement_end_offset END 
                       - sdeqs.statement_start_offset)/2) + 1) AS SQL_Statement 
       , sdeqp.query_plan  
       , sdeqs.execution_count 
       , sdeqs.total_physical_reads 
       ,(sdeqs.total_physical_reads / sdeqs.execution_count) AS average_physical_reads 
       , sdeqs.total_logical_writes 
       , (sdeqs.total_logical_writes / sdeqs.execution_count) AS average_logical_writes 
       , sdeqs.total_logical_reads 
       , (sdeqs.total_logical_reads / sdeqs.execution_count) AS average_logical_lReads 
       , sdeqs.total_clr_time 
       , (sdeqs.total_clr_time / sdeqs.execution_count) AS average_CLRTime 
       , sdeqs.total_elapsed_time 
       , (sdeqs.total_elapsed_time / sdeqs.execution_count) AS average_elapsed_time 
       , sdeqs.last_execution_time 
       , sdeqs.creation_time  
FROM sys.dm_exec_query_stats AS sdeqs 
       CROSS apply sys.dm_exec_sql_text(sdeqs.sql_handle) AS sdest 
       CROSS apply sys.dm_exec_query_plan(sdeqs.plan_handle) AS sdeqp 
WHERE  sdeqs.last_execution_time > DATEADD(HH,-2,GETDATE())  
               --AND sdest.dbid = (SELECT DB_ID('AdventureWorks')) 
ORDER BY execution_count DESC  


-------------------------------
-- Query stats by avg cpu time
-------------------------------   
SELECT TOP 5 query_stats.query_hash AS "Query Hash", 
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM 
    (SELECT QS.*, 
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE QS.statement_end_offset END 
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;