Just Code‎ > ‎

TSQL - Peek into what Queries/Statements are in the query plan cache, and their execution plan if available.

posted Aug 29, 2012, 5:09 AM by Peter Henell   [ updated Aug 29, 2012, 5:12 AM ]
SELECT  [AVG CPU per execution] = total_worker_time / execution_count,
        [Total CPU] = total_worker_time,
        [AVG Duration per execution] = total_elapsed_time / execution_count,
        [Total Duration] = total_elapsed_time ,
        [AVG READS per executoin] = total_logical_reads / execution_count ,
        [Total READS] = total_logical_reads ,
        [Execution COUNT] = execution_count ,
        [SQL TEXT] = st.txt ,
        [Query PLAN] = query_plan
FROM    
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle)     AS s
CROSS APPLY
    ( 
        SELECT
            SUBSTRING(s.text , ( qs.statement_start_offset / 2 ) + 1,
              ( ( CASE qs.statement_end_offset
                    WHEN -1 THEN DATALENGTH(s.text)
                    ELSE qs.statement_end_offset
                  END - qs.statement_start_offset ) / 2 ) + 1) AS txt        
    ) AS st(txt)
CROSS APPLY 
    sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE    
    st.txt LIKE '%' -- Option to filter and group by the sql text of each statement

ORDER BY 1 DESC

Comments