denrepo

SQL ServerPerformance

Top 20 statements by CPU

Cached statements ranked by total CPU since they entered the plan cache. Compare avg_cpu_ms with execution_count to separate one heavy query from a chatty one.

Not yet verified. How scripts are tested

b-mssql-topcpu.sql 
1SELECT TOP (20)2       qs.total_worker_time / 1000 AS total_cpu_ms,3       qs.execution_count,4       qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,5       qs.total_logical_reads / qs.execution_count AS avg_logical_reads,6       DB_NAME(t.dbid) AS database_name,7       SUBSTRING(t.text, (qs.statement_start_offset / 2) + 1,8         ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)9           ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,10       qs.last_execution_time11FROM sys.dm_exec_query_stats qs12CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t13ORDER BY qs.total_worker_time DESC;

Paste it into your query tool.

Open in denrepo

More SQL Server scripts: Performance