Monitoring · Performance · SQL Server

Practical Query Store Usage for Performance Troubleshooting

2 min read

Query Store preserves query text, plans and runtime history inside the database. Its real value is not a single “top queries” screen; it is the ability to compare behavior before and after a known point in time.

[company_article_image src=”articles/query-store-workflow.svg” alt=”Query Store workflow capturing, comparing and deciding on query plan regressions” caption=”Query Store connects runtime intervals to query text and plan history.”]

Configure it as an operational component

Set an appropriate maximum size, retention policy and capture mode. Monitor actual state and space usage so Query Store does not silently become read-only. Configuration should reflect workload volume and troubleshooting needs.

SELECT actual_state_desc, desired_state_desc,
       current_storage_size_mb, max_storage_size_mb,
       readonly_reason
FROM sys.database_query_store_options;

Investigate a regression

  1. Define the slowdown window and relevant application action.
  2. Find queries whose duration, CPU or logical reads changed materially.
  3. Compare plan IDs and runtime intervals.
  4. Inspect statistics, cardinality estimates, parameter sensitivity, spills and index changes.
  5. Validate a correction against representative parameters.

Find high-variance candidates

SELECT TOP (20)
       q.query_id,
       p.plan_id,
       SUM(rs.count_executions) AS executions,
       AVG(rs.avg_duration) AS avg_duration_microseconds,
       MAX(rs.max_duration) AS max_duration_microseconds
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
  ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
  ON rs.plan_id = p.plan_id
GROUP BY q.query_id, p.plan_id
ORDER BY MAX(rs.max_duration) DESC;

Apply an explicit time-window predicate through sys.query_store_runtime_stats_interval in production investigations. Without it, old peaks can dominate the result and obscure the incident.

Use plan forcing as a controlled mitigation

Forcing a known good plan may restore stability while the underlying cause is investigated, but it is not maintenance-free. Record why the plan was forced, monitor failures and define when it will be reviewed or removed.

Measure the outcome

Compare equivalent post-change intervals and check more than elapsed time: CPU, logical reads, execution count and wait profile matter. Query Store turns “it became slow” into a timeline that can be tested.

Read Microsoft’s Query Store monitoring guidance.

Related Articles