1. What Makes a SQL Server Query Expensive?

A query is not necessarily expensive just because it takes several seconds to execute.

Different queries consume different types of resources. A query that executes very frequently may have a significant cumulative impact even if each execution is relatively fast.

Some important performance metrics include:

CPU Consumption
Indicates how much processor time SQL Server spends executing a query. Queries with high CPU consumption may involve expensive calculations, joins, sorting, aggregations or inefficient execution plans.

Execution Duration
Measures how long a query takes to execute. Long execution times can be associated with blocking, resource waits, inefficient plans, storage latency and other factors.

Logical Reads
Represent the number of 8 KB data pages read from the buffer pool. High logical reads can indicate excessive scanning, inefficient joins or opportunities for query and index optimization.

Physical Reads
Represent data pages read from physical storage rather than satisfied from the buffer pool. High physical reads can contribute to storage I/O pressure.

Execution Count
Represents how many times a query was executed during a particular Query Store interval. A query with relatively low resource consumption per execution can still become expensive when executed thousands of times.

Memory Consumption
Query Store also captures memory-related runtime statistics that can help identify queries with substantial memory requirements.

These metrics should be considered together rather than relying on a single measurement.

2. Understanding SQL Server Query Store Runtime Statistics

Query Store captures query text, execution plans and runtime statistics.

Its information is exposed through several catalog views:

Query Store viewPurpose
sys.query_store_query_textStores captured SQL query text
sys.query_store_queryContains query-level information
sys.query_store_planContains execution plan information
sys.query_store_runtime_statsContains aggregated execution statistics
sys.query_store_runtime_stats_intervalDefines runtime statistics collection intervals
sys.query_store_wait_statsContains query-level wait statistics when supported and enabled

The relationship between these views is important. A query can have multiple execution plans, and each plan can have runtime statistics across multiple time intervals. This means a DBA can investigate not only how a query performs now, but also how its performance has changed over time.

Query Store aggregates runtime statistics over configured intervals. It provides average, minimum, maximum and standard deviation metrics for supported statistics. See Microsoft documentation: How Query Store collects data.

Technical notes: Query Store runtime duration and CPU values are in microseconds; the examples convert them to milliseconds. Runtime statistics are aggregated by plan and time interval. The examples filter successful executions (execution_type = 0). Interval timestamps are datetimeoffset; the samples use UTC. These rankings are exploratory—not exact per-execution accounting—and an interval overlapping the 24-hour boundary can include some statistics from outside that exact period.

Check whether Query Store is enabled

Before running the examples, check the Query Store status.

T-SQL
SELECT
    name,
    is_query_store_on
FROM sys.databases
ORDER BY name;
GO

If Query Store is disabled for the database you want to investigate, enable it before new collection can begin:

T-SQL
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON;
GO

Enabling Query Store does not immediately provide historical data for executions that occurred before collection was enabled.

3. Find Queries with the Highest Total CPU Consumption

CPU-intensive queries can contribute significantly to high SQL Server processor utilization. The following query identifies statements with the highest cumulative CPU consumption during the last 24 hours. Run it in the context of the database being investigated.

T-SQL
USE YourDatabase;
GO

SELECT TOP (20)
    q.query_id,
    p.plan_id,
    SUM(rs.count_executions) AS execution_count,
    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.last_execution_time) AS last_execution_time,
    qt.query_sql_text
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_query_text AS qt
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rs.runtime_stats_interval_id =
       rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
  AND rs.execution_type = 0
GROUP BY
    q.query_id,
    p.plan_id,
    qt.query_sql_text
ORDER BY total_cpu_ms DESC;
GO

Understanding the results

  • query_id: Query Store identifier for the query.
  • plan_id: Execution plan identifier.
  • execution_count: Number of successful executions in the selected intervals.
  • total_cpu_ms: Estimated cumulative CPU time in milliseconds.
  • avg_cpu_ms: Average CPU time per execution in milliseconds.
  • last_execution_time: Last execution time represented by the runtime statistics.
  • query_sql_text: Captured SQL statement text.

Why total CPU and average CPU matter

QueryAverage CPUExecutionsTotal CPU
Query A500 ms105,000 ms
Query B10 ms10,000100,000 ms

Query A is more expensive per execution, but Query B consumes considerably more cumulative CPU. Both deserve investigation, depending on the performance objective.

4. Find Queries with the Longest Execution Duration

Long-running queries can affect application response times and consume database resources for extended periods. The following query identifies statements with the highest cumulative execution duration during the last 24 hours.

T-SQL
USE YourDatabase;
GO

SELECT TOP (20)
    q.query_id,
    p.plan_id,
    SUM(rs.count_executions) AS execution_count,
    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,
    MAX(rs.last_execution_time) AS last_execution_time,
    qt.query_sql_text
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_query_text AS qt
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rs.runtime_stats_interval_id =
       rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
  AND rs.execution_type = 0
GROUP BY
    q.query_id,
    p.plan_id,
    qt.query_sql_text
ORDER BY total_duration_ms DESC;
GO

Query Store reports duration in microseconds, so the examples convert it into milliseconds. Total duration is not necessarily the same as CPU consumption—a query can have high duration because it spends time waiting for resources.

5. Find Queries with the Highest Logical Reads

Logical reads are another important metric when investigating SQL Server performance. A query that reads a large number of pages may be performing unnecessary scans or processing more data than required.

T-SQL
USE YourDatabase;
GO

SELECT TOP (20)
    q.query_id,
    p.plan_id,
    SUM(rs.count_executions) AS execution_count,
    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,
    qt.query_sql_text
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_query_text AS qt
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rs.runtime_stats_interval_id =
       rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
  AND rs.execution_type = 0
GROUP BY
    q.query_id,
    p.plan_id,
    qt.query_sql_text
ORDER BY total_logical_reads DESC;
GO

What should you investigate?

When a query has unusually high logical reads, investigate table and index scans, missing or unsuitable indexes, inefficient joins, non-SARGable predicates, excessive rows being processed, and incorrect cardinality estimates.

High logical reads do not automatically mean a query is poorly written. Large analytical queries may legitimately read many pages. Always compare the resource consumption with the expected workload.

6. Find Queries with High Physical Reads

Physical reads can indicate that SQL Server needed to retrieve pages from storage rather than finding them in the buffer pool.

T-SQL
USE YourDatabase;
GO

SELECT TOP (20)
    q.query_id,
    p.plan_id,
    SUM(rs.count_executions) AS execution_count,
    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.last_execution_time) AS last_execution_time,
    qt.query_sql_text
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_query_text AS qt
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rs.runtime_stats_interval_id =
       rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
  AND rs.execution_type = 0
GROUP BY
    q.query_id,
    p.plan_id,
    qt.query_sql_text
ORDER BY total_physical_reads DESC;
GO

High physical reads can indicate storage pressure or inefficient access patterns, but the buffer pool and workload context must also be considered.

7. Find Queries with the Highest Execution Counts

Sometimes the most expensive workload is not caused by one unusually slow query. A query executed thousands of times can consume substantial cumulative resources even if each execution is relatively inexpensive.

T-SQL
USE YourDatabase;
GO

SELECT TOP (20)
    q.query_id,
    p.plan_id,
    SUM(rs.count_executions) AS total_executions,
    AVG(rs.avg_cpu_time) / 1000.0 AS avg_cpu_ms,
    AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
    qt.query_sql_text
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_query_text AS qt
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rs.runtime_stats_interval_id =
       rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
  AND rs.execution_type = 0
GROUP BY
    q.query_id,
    p.plan_id,
    qt.query_sql_text
ORDER BY total_executions DESC;
GO

A high execution count is not automatically a performance problem. However, frequently executed queries can be good candidates for optimization when their cumulative resource consumption is significant.

8. Why You Should Analyze More Than One Metric

Looking at CPU alone may not reveal the complete performance problem.

QueryTotal CPUTotal DurationLogical ReadsExecutions
Query AHighMediumHighLow
Query BMediumHighMediumMedium
Query CMediumMediumVery highHigh
Query DLowLowLowVery high
  • Query A may require CPU and execution plan investigation.
  • Query B may be waiting on resources.
  • Query C may benefit from reviewing its data access strategy.
  • Query D may deserve investigation because of its execution frequency.

The right tuning approach depends on the underlying cause, not simply the query's ranking in one metric.

9. Investigate the Execution Plan

After identifying an expensive query, inspect its execution plan. Look for potentially expensive operations such as table scans, index scans, large sorts, hash matches, nested loops with excessive executions, key lookups, implicit conversions, and large differences between estimated and actual row counts.

Also review indexes, statistics, parameter values and the query's workload context. Query Store retains plan history, which is particularly useful when investigating whether a query has changed its execution plan over time.

Remember that Query Store stores compile-time plan information; an actual execution plan with runtime row counts and warnings may require separate collection.

10. Limitations of Manual Query Store Analysis

The T-SQL examples in this article are useful for investigating individual databases. However, Query Store is database-scoped.

If an instance contains 30 or 50 databases, a DBA may need to repeat these queries in each database to identify the most expensive queries across the environment.

This introduces several challenges:

  • Repeated execution of diagnostic scripts.
  • Manual comparison of database results.
  • Difficulty identifying the highest-impact database first.
  • Additional effort maintaining reports.
  • Limited visibility across the complete SQL Server workload.

For the complementary multi-database workflow—finding which database needs attention before drilling into queries—see How to Analyze SQL Server Query Store Across Multiple Databases.

11. Simplify Multi-Database Query Store Analysis with DBLense

SQL Server Query Store Analyzer addresses this challenge by consolidating Query Store information from multiple databases into one centralized application interface.

Instead of starting with an individual database, DBAs can begin with a consolidated overview:

  1. Connect to the SQL Server instance.
  2. Review the centralized database performance overview.
  3. Identify databases with high resource consumption.
  4. Select a database for deeper analysis.
  5. Investigate expensive queries and execution plans.
  6. Review performance comparisons and tuning recommendations.
DBLense Query Store Analyzer Overview showing Database Execution Summary across multiple databases
Centralized Overview: identify which databases deserve investigation first.
DBLense Query Store Analyzer Queries page with query text, plan summary, and execution statistics
Query investigation: review statement text, plan summary, and runtime statistics after a database is selected.
DBLense Query Store Analyzer Execution Plan visualization with operator costs
Execution plan visualization: inspect operators and costs for an expensive query.

The application also provides day-over-day comparisons, regressed query investigation, missing and unused index analysis, performance tuning rules, and root cause analysis. Query Store must be enabled on databases from which Query Store data is to be collected.

The objective is simple: identify where the performance problem exists first, then investigate the queries responsible for it.

12. Conclusion

Finding the most expensive SQL Server queries requires more than identifying the statement with the highest execution duration. DBAs should evaluate CPU consumption, logical reads, physical reads, execution frequency and execution plans together.

SQL Server Query Store provides valuable historical information that can help identify expensive queries and investigate performance changes. The T-SQL examples in this article provide a starting point for database-level analysis.

For environments with multiple databases, SQL Server Query Store Analyzer provides a centralized way to review Query Store information, identify problematic databases and investigate expensive queries from one interface.

Explore Centralized Query Store Analysis

Try DBLense Query Store Analyzer with a free 14-day trial.

Download Free Trial SQL Server Query Store Analyzer View Documentation Multi-Database Guide

Frequently Asked Questions

How do I find the most expensive queries in SQL Server?

You can use Query Store runtime statistics to identify queries with high CPU consumption, execution duration, logical reads and execution counts. SQL Server also provides built-in Query Store reports in SSMS.

How do I find high CPU queries using Query Store?

Query sys.query_store_runtime_stats and join it to the Query Store query, plan and runtime interval views. Aggregate CPU statistics across the selected time range and sort by total CPU consumption.

How do I find slow queries in SQL Server?

Use Query Store to review average and maximum execution duration, execution counts and historical plan information. For currently executing queries, DMVs and other live monitoring tools can provide additional information.

What is the difference between CPU time and duration?

CPU time represents the processor time used by a query. Duration represents the elapsed execution time. A query can have high duration but relatively low CPU consumption when it spends time waiting for resources.

Does Query Store collect historical query performance data?

Yes. Query Store retains query, plan and aggregated runtime statistics according to its configuration and retention settings. It can help investigate query performance beyond the lifetime of the plan cache.

Can I analyze Query Store across multiple databases?

SQL Server Query Store is database-scoped. You can query individual databases separately or use a tool such as SQL Server Query Store Analyzer to consolidate supported Query Store information into one interface. See also analyzing Query Store across multiple databases.

Can Query Store identify execution plan regressions?

Yes. Query Store maintains plan history and runtime statistics that can help identify performance changes associated with plan changes.

Is Query Store available in Azure SQL Database?

Yes. Query Store is available in Azure SQL Database and Azure SQL Managed Instance, subject to the platform's supported features and configuration.

Microsoft References

← All articles · Analyze Query Store across multiple databases · SQL Server Query Store Analyzer