Rank Expensive Queries with sys.query_store_runtime_stats

calendar Jul 26, 2026 / · 9 min read · sql server query store sys.query_store_runtime_stats sys.query_store_plan sys.query_store_query sys.query_store_query_text sys.query_store_runtime_stats_interval performance monitoring scripts  ·
Share on: linkedin copy

Query Store buckets runtime statistics into fixed time intervals, which means a query that ran steadily all week doesn't produce one row of history — it produces one row per interval, split further by execution outcome. Averaging those rows unweighted is how a "most expensive queries" report quietly gets the ranking wrong on a busy instance. sys.query_store_runtime_stats is the catalog view holding that interval-level detail, and joined correctly to plan, query, and query text — with a weighted rollup across execution counts — it produces the resource-consumption ranking a DBA actually wants.

Purpose and Overview

Query Store, available since SQL Server 2016, persists execution history to disk inside the user database itself rather than holding it only in memory — the key difference from server-scope views like sys.dm_exec_query_stats, which reset when a plan is evicted from cache or the service restarts. That persistence is what makes Query Store useful for finding a query that spiked resource usage overnight, or after a deployment three days ago: the history survives long after the plan cache has moved on to other work.

sys.query_store_runtime_stats is the catalog view at the center of that history. Each row covers one plan_id, one time bucket from sys.query_store_runtime_stats_interval, and one execution_type — meaning a single plan executed steadily across ten one-hour buckets produces ten separate rows, each already averaged within its own bucket. Finding the truly most expensive queries means combining those rows correctly, not simply picking the single highest row or averaging the averages across buckets that may have wildly different execution counts.

The script below does that combination in two passes: a plan-level rollup that surfaces the specific compiled plan driving the cost, and a query-level rollup that sums across every plan a query has run under — catching the case where a query's optimizer keeps switching between two mediocre plans that individually look unremarkable but combined dominate the workload.

Code Breakdown

The first query rolls runtime stats up to the plan level, weighting each interval's average by its execution count before combining, then joins through to the query's SQL text.

 1SELECT TOP 25
 2    qsq.query_id,
 3    OBJECT_NAME(qsq.object_id)                                      AS object_name,
 4    qsqt.query_sql_text,
 5    qsp.plan_id,
 6    SUM(rs.count_executions)                                        AS total_executions,
 7    CAST(SUM(rs.avg_duration * rs.count_executions)
 8        / NULLIF(SUM(rs.count_executions), 0) AS DECIMAL(18,2))     AS weighted_avg_duration_us,
 9    CAST(SUM(rs.avg_cpu_time * rs.count_executions)
10        / NULLIF(SUM(rs.count_executions), 0) AS DECIMAL(18,2))     AS weighted_avg_cpu_time_us,
11    CAST(SUM(rs.avg_logical_io_reads * rs.count_executions)
12        / NULLIF(SUM(rs.count_executions), 0) AS DECIMAL(18,2))     AS weighted_avg_logical_reads,
13    CAST(SUM(rs.avg_duration * rs.count_executions) / 1000.0 AS DECIMAL(18,2))  AS total_duration_ms,
14    CAST(SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0 AS DECIMAL(18,2))  AS total_cpu_time_ms,
15    MIN(rsi.start_time)                                             AS earliest_interval,
16    MAX(rsi.end_time)                                               AS latest_interval
17FROM sys.query_store_runtime_stats AS rs
18INNER JOIN sys.query_store_runtime_stats_interval AS rsi
19    ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
20INNER JOIN sys.query_store_plan AS qsp
21    ON rs.plan_id = qsp.plan_id
22INNER JOIN sys.query_store_query AS qsq
23    ON qsp.query_id = qsq.query_id
24INNER JOIN sys.query_store_query_text AS qsqt
25    ON qsq.query_text_id = qsqt.query_text_id
26WHERE rs.execution_type = 0
27    AND rsi.start_time >= DATEADD(HOUR, -24, SYSUTCDATETIME())
28GROUP BY
29    qsq.query_id,
30    qsq.object_id,
31    qsqt.query_sql_text,
32    qsp.plan_id
33ORDER BY total_cpu_time_ms DESC;

A second query drops plan_id from the grouping to roll all the way up to the query level, catching queries whose cost is spread across more than one compiled plan:

 1SELECT TOP 25
 2    qsq.query_id,
 3    OBJECT_NAME(qsq.object_id)                                      AS object_name,
 4    qsqt.query_sql_text,
 5    COUNT(DISTINCT qsp.plan_id)                                     AS distinct_plans,
 6    SUM(rs.count_executions)                                        AS total_executions,
 7    CAST(SUM(rs.avg_cpu_time * rs.count_executions)
 8        / NULLIF(SUM(rs.count_executions), 0) AS DECIMAL(18,2))     AS weighted_avg_cpu_time_us,
 9    CAST(SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0 AS DECIMAL(18,2))  AS total_cpu_time_ms
10FROM sys.query_store_runtime_stats AS rs
11INNER JOIN sys.query_store_runtime_stats_interval AS rsi
12    ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
13INNER JOIN sys.query_store_plan AS qsp
14    ON rs.plan_id = qsp.plan_id
15INNER JOIN sys.query_store_query AS qsq
16    ON qsp.query_id = qsq.query_id
17INNER JOIN sys.query_store_query_text AS qsqt
18    ON qsq.query_text_id = qsqt.query_text_id
19WHERE rs.execution_type = 0
20    AND rsi.start_time >= DATEADD(DAY, -7, SYSUTCDATETIME())
21GROUP BY
22    qsq.query_id,
23    qsq.object_id,
24    qsqt.query_sql_text
25ORDER BY total_cpu_time_ms DESC;

Weighting by count_executions across interval buckets

avg_duration, avg_cpu_time, and avg_logical_io_reads are each already an average scoped to one interval bucket — by default a 60-minute window, though the length is configurable and can vary across a database's history if it was changed at some point. A plan that ran in ten buckets contributes ten rows, and those buckets rarely carry equal execution counts: a slow overnight batch window might contribute one row from three executions, while a busy afternoon bucket contributes one row from three thousand. A plain AVG() across those ten rows treats both buckets equally, letting a handful of low-volume outlier intervals distort the ranking. Multiplying each bucket's average by its count_executions, summing across buckets, and dividing by the summed execution count produces a properly weighted mean — the number that actually reflects what happened across the full population of executions, not across the population of buckets.

Filtering to execution_type = 0

execution_type distinguishes how each set of executions ended: 0 for Regular (completed normally), 1 for Aborted (execution stopped, often a client-side cancellation or timeout), and 2 for Exception (ended due to a runtime error). Restricting the ranking to execution_type = 0 keeps the "most expensive successful executions" framing clean — an aborted execution's partial duration and I/O don't represent the query's real cost when it runs to completion. Chasing timeout complaints specifically is a different question, and it calls for widening the filter to include 1 rather than dropping it.

Chaining runtime_stats through plan, query, and query text

The four-table join follows Query Store's own object hierarchy: sys.query_store_runtime_stats rows belong to a plan_id in sys.query_store_plan, which belongs to a query_id in sys.query_store_query, which points at a query_text_id in sys.query_store_query_text. The SQL text lives in its own table rather than inline on the query row because Query Store separates the identity of a compiled query from the raw text it was compiled from — a normalization choice that keeps the more frequently updated query and plan metadata rows narrow. OBJECT_NAME(qsq.object_id) resolves the owning stored procedure or function when the query came from a module; for ad hoc statements outside any object, object_id is 0 and the function returns NULL, which is expected and not a join failure.

Rolling up across plan_id to the query level

The second query removes qsp.plan_id from the GROUP BY and adds COUNT(DISTINCT qsp.plan_id) instead, collapsing every plan a query_id has run under into a single row. This matters because Query Store can hold multiple plans for the same logical query — the optimizer recompiled it after a statistics update, a parameter-sniffing edge case produced an alternate plan, or a schema change triggered a new compile. Each individual plan might rank modestly in the plan-level report; summed together at the query level, the same query can surface as a genuine top consumer. distinct_plans in the output is itself a signal worth watching — a high count on a query that should be stable often points at plan instability rather than a workload change.

Scoping the analysis window with runtime_stats_interval

sys.query_store_runtime_stats_interval stores the start_time and end_time boundaries for each bucket, independent of the runtime_stats rows themselves. Filtering on rsi.start_time scopes the ranking to a rolling window — the last 24 hours in the plan-level query, the last 7 days in the query-level rollup — rather than the entire history Query Store's retention policy still holds. Without that filter, a query that spiked once months ago and hasn't run since can still outrank today's genuinely active workload, because the totals accumulate across the full retention window rather than the period actually under investigation.

Key Benefits and Use Cases

  • Survives restarts — persisted to disk in the database itself, unlike plan-cache views that reset on service restart or plan eviction.
  • Weighted accuracy — combining interval buckets by execution count avoids the skew a naive AVG(avg_duration) introduces on uneven workloads.
  • Plan-level and query-level views — the two-query pattern above catches both a single bad plan and a query whose cost is spread across several plans.
  • Built for regression triage — pairing plan_id with sys.query_store_plan.query_plan pinpoints exactly which compiled plan is responsible, not just which query.
  • No extra collection setup — once Query Store is enabled, this data collects automatically with no Extended Events session or trace to configure.
  • Configurable lookback — filtering on runtime_stats_interval.start_time scopes analysis to anything the retention policy still holds, from the last hour to the full history.

Performance Considerations

  • Requires Query Store enabled and is database-scoped: unlike most server-scope DMVs, these catalog views only return data once ALTER DATABASE ... SET QUERY_STORE = ON has been run, and the query must execute in the context of the specific database being investigated.
  • Retention is capacity- and age-governed: CLEANUP_POLICY and MAX_STORAGE_SIZE_MB control how much history persists; once the size cap is reached with SIZE_BASED_CLEANUP_MODE = AUTO, Query Store purges the oldest interval data first, silently shortening the lookback window a report like this can see.
  • A full store can go read-only: if SIZE_BASED_CLEANUP_MODE is OFF and the size cap is hit, Query Store stops capturing new runtime stats until space is freed or the cap is raised — a report that looks current may actually reflect a frozen snapshot.
  • Interval length is fixed per bucket: INTERVAL_LENGTH_MINUTES applies to buckets created after the setting changes; a database's history can contain buckets of different lengths side by side if the setting was ever adjusted.
  • Recent executions may lag: DATA_FLUSH_INTERVAL_SECONDS controls how often in-memory runtime stats are written to the on-disk Query Store tables, so the very latest executions may not appear in sys.query_store_runtime_stats until the next flush.

Practical Tips

  • Cross-check this script's output against the built-in Top Resource Consuming Queries report in SQL Server Management Studio's Query Store folder before trusting a fully custom ranking on an unfamiliar instance.
  • Once a top consumer is identified, pull qsp.query_plan from sys.query_store_plan for that plan_id to open the graphical plan and confirm which operator is actually driving the cost.
  • If a query's cost traces back to plan instability rather than a bad query, sys.sp_query_store_force_plan pins a known-good plan_id without a code change or index rebuild.
  • Widen the execution_type filter to include 1 (Aborted) when chasing timeout complaints specifically — those executions never reach the Regular bucket and stay invisible to a report scoped to execution_type = 0 alone.
  • Schedule both rollups as a recurring SQL Server Agent job writing to a history table so week-over-week resource-consumption trends are visible instead of only a single point-in-time snapshot.

Conclusion

sys.query_store_runtime_stats, rolled up correctly through sys.query_store_plan, sys.query_store_query, and sys.query_store_query_text, turns Query Store's interval-bucketed history into a straight resource-consumption ranking — plan by plan or query by query. The weighted aggregation is the detail that separates an accurate top-consumers report from one that quietly overweights low-volume intervals, and it belongs beside wait-statistics and buffer-pool queries in any SQL Server performance-tuning toolkit.

References

Posts in this series