Post date: Aug 29, 2012 12:09:38 PM
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 sCROSS 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 statementORDER BY 1 DESC