Finding the Most Resource-Intensive Queries in SQL Server

Identifying queries that consume the most resources can help you prioritize performance optimization efforts. In SQL Server you can use Query Store to track CPU time, duration, memory usage, and I/O for every query. This makes it easy to find the biggest resource consumers in your database.

You can use the following query to find queries with the highest total CPU consumption:

SELECT TOP 20
    q.query_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS total_executions,
    SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0 AS total_cpu_ms,
    AVG(rs.avg_cpu_time) / 1000.0 AS avg_cpu_ms,
    MAX(rs.max_cpu_time) / 1000.0 AS max_cpu_ms
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_cpu_ms DESC;

This returns the top 20 queries by total CPU time consumed, which is often the best indicator of which queries are impacting overall system performance.

Finding Queries by Duration

To find queries that take the longest to execute:

SELECT TOP 20
    q.query_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS total_executions,
    SUM(rs.avg_duration * rs.count_executions) / 1000.0 AS total_duration_ms,
    AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
    MAX(rs.max_duration) / 1000.0 AS max_duration_ms
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_duration_ms DESC;

Duration includes wait time, so queries with high duration but low CPU might be waiting on locks or I/O rather than consuming compute resources.

Finding Queries by Logical Reads

Logical reads indicate how much data a query reads from memory. High logical reads often correlate with performance problems:

SELECT TOP 20
    q.query_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS total_executions,
    SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_logical_reads,
    AVG(rs.avg_logical_io_reads) AS avg_logical_reads,
    MAX(rs.max_logical_io_reads) AS max_logical_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_logical_reads DESC;

Queries with millions of logical reads are often candidates for index tuning or query optimization.

Finding Queries by Physical Reads

Physical reads indicate disk I/O, which is much slower than memory access:

SELECT TOP 20
    q.query_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS total_executions,
    SUM(rs.avg_physical_io_reads * rs.count_executions) AS total_physical_reads,
    AVG(rs.avg_physical_io_reads) AS avg_physical_reads,
    MAX(rs.max_physical_io_reads) AS max_physical_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_physical_reads DESC;

High physical reads suggest the query is accessing data that’s not in the buffer cache, potentially due to insufficient memory or poor indexing.

Finding Queries by Execution Count

Sometimes you can achieve the biggest performance improvements by optimizing frequently executed queries rather than focusing solely on slow queries. A query that takes 10ms but runs 100,000 times per hour has more impact than a query that takes 1 second but runs once per hour.

This query sorts by total executions:

SELECT TOP 20
    q.query_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS total_executions,
    AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
    SUM(rs.avg_duration * rs.count_executions) / 1000.0 AS total_duration_ms
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_executions DESC;

Filtering by Time Period

Sometimes you might want to narrow the results to just recent queries. For example, you might do the following to focus on recent resource consumption:

SELECT TOP 20
    q.query_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS total_executions,
    SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0 AS total_cpu_ms,
    AVG(rs.avg_cpu_time) / 1000.0 AS avg_cpu_ms
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(day, -1, GETUTCDATE())
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_cpu_ms DESC;

This shows only the last day’s resource consumption, helping you identify current bottlenecks rather than historical issues.

Combining Multiple Metrics

Of course, you can add or change the columns for a more comprehensive view of resource-intensive queries:

SELECT TOP 20
    q.query_id,
    LEFT(qt.query_sql_text, 100) + '...' AS query_preview,
    SUM(rs.count_executions) AS executions,
    SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0 AS total_cpu_ms,
    SUM(rs.avg_duration * rs.count_executions) / 1000.0 AS total_duration_ms,
    SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_logical_reads,
    SUM(rs.avg_physical_io_reads * rs.count_executions) AS total_physical_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_cpu_ms DESC;

This shows multiple resource metrics in a single view, helping you understand the full impact of each query.