dataiotech Labs
Scripts Library/sqlserver/#42
Script #42
sqlserver
Performanceread-onlyMin Version: 2012

Top CPU-Consuming Queries in SQL Server

Lists the top 20 queries by total and average CPU time from the plan cache. Pinpoints the source of CPU pressure at query level.

#query-stats#cpu#top-queries
sqlserver · sql
SELECT TOP 20
       qs.total_worker_time / 1000 AS total_cpu_ms,
       qs.execution_count,
       qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
       qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
       qs.last_execution_time,
       SUBSTRING(t.text, (qs.statement_start_offset/2)+1,
         ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
           ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS statement_text,
       DB_NAME(t.dbid) AS db_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY qs.total_worker_time DESC;
Operational Notes & Remediation Guidance

High CPU queries frequently suffer from missing indexes or excessive scalar function invocations in WHERE clauses.

Production Safety & Execution Standards
  • This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
  • Prior to executing on production systems under high load, verify the target database version (Min. Version: 2012).
  • Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.

Engineering Triad: Related Runbooks & Incident Response

Which production incidents and emergency drills execute this script as a diagnosis step?

Runbook Library (63)
Related Alerts & Metrics
M26SQL Server Memory Grants PendingSQL Server
M27SQL Server CPU UtilizationSQL Server