SQL Server: top statements by logical I/O and CPU
List the cached statements that cost the most logical reads, logical writes or CPU time.
This query lists the 10 most expensive statements according to the execution statistics of the plan cache (sys.dm_exec_query_stats). By default the sort is on logical reads; the other sorts are given as comments. It also returns the statement text and its execution plan.
Query
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.total_logical_reads DESC -- logical reads -- ORDER BY qs.total_logical_writes DESC -- logical writes -- ORDER BY qs.total_worker_time DESC -- CPU time
Notes
- The statistics only cover the plans still in the cache: they disappear when a plan is removed from the cache or when the instance restarts.
- To sort by logical writes or CPU time, replace the
ORDER BYclause with the matching commented line.