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.
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;High CPU queries frequently suffer from missing indexes or excessive scalar function invocations in WHERE clauses.
- 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?
SQL Server Memory Pressure: PLE Drops and Memory Grants Pending
Finds the cause of low Page Life Expectancy and pending memory grants: wrong max server memory, large query grants, missing indexes and stale statistics.
SQL Server CPU 100%: sp_WhoIsActive, Query Store & Top Queries Triage
Emergency triage when SQL Server or Azure SQL CPU spikes >90%: live session analysis via sp_WhoIsActive, Query Store plan regressions, parameter sniffing plan forcing, and Resource Governor mitigations.
Other SQLSERVER Diagnostic Scripts
View All (100)SQL Server Running Requests and Blocking Chain
Shows active requests, wait types, blocking sessions and the running statement together. The first diagnostic query to run during a slowdown.
SQL Server Missing Index Recommendations (Missing Index DMVs)
Ranks the optimizer's missing index suggestions by estimated benefit. Merge them with existing indexes before applying.
SQL Server Index Fragmentation with Rebuild/Reorganize Advice
Measures fragmentation on large indexes and recommends rebuild or reorganize. Lets you plan maintenance windows without wasted work.
SQL Server Data and Log File Size, Free Space and Autogrowth
Shows file size, used and free space, and growth settings. Used for capacity planning and catching bad autogrowth configs.