Following query will return TOP 100 worst performing SQL Statement with the name of object that contain those statements e.g. Stored Procedure, Trigger and Function. Current database context will be used so change the database before executing query. The result will be sorted by CPU time in descending order.
SELECT TOP 100 [Object_Name] = object_name(st.objectid), creation_time, last_execution_time, total_cpu_time = total_worker_time / 1000, avg_cpu_time = (total_worker_time / execution_count) / 1000, min_cpu_time = min_worker_time / 1000, max_cpu_time = max_worker_time / 1000, last_cpu_time = last_worker_time / 1000, total_time_elapsed = total_elapsed_time / 1000 , avg_time_elapsed = (total_elapsed_time / execution_count) / 1000, min_time_elapsed = min_elapsed_time / 1000, max_time_elapsed = max_elapsed_time / 1000, avg_physical_reads = total_physical_reads / execution_count, avg_logical_reads = total_logical_reads / execution_count, execution_count, 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 qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE Object_Name(st.objectid) IS NOT NULL AND st.dbid = DB_ID() ORDER BY db_name(st.dbid), total_worker_time / execution_count DESC
Also, we can also use the DMV to extract cached query plans in XML format which is much useful to identify any bad query plans were used in those bad performing queries.
select st.text, qp.query_plan, cp.cacheobjtype, cp.objtype, cp.plan_handle from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_sql_text(cp.plan_handle) st cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
No Comments