How to Use SQL Server Query Store to Find Regressed Queries

Introduction

Have you ever worked on an application where a SQL query was running quickly yesterday but suddenly became slow today?

The application code has not changed, the database is still available, and yet users are reporting longer response times. After investigating, you discover that a query that used to finish quickly is now taking much longer to execute.

This can happen for several reasons. SQL Server may choose a different execution plan, the amount of data may have changed, statistics may have been updated, or the database may be experiencing resource pressure.

Finding the exact cause can be difficult if you have no record of how the query performed previously.

SQL Server Query Store helps solve this problem by keeping a history of query text, execution plans, and runtime statistics. You can use this information to compare query performance over time and identify queries that have become slower.

In this article, we will learn how to enable Query Store, find queries with performance regressions, compare execution plans, and troubleshoot common performance problems.

What Is SQL Server Query Store?

Query Store is a SQL Server feature that collects information about queries executed against a database.

It records query text, execution plans, and runtime statistics so that you can investigate how query performance changes over time.

For example, imagine an application uses the following query to retrieve customer orders:

SELECT
    OrderId,
    CustomerId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId;

The query might initially use an index seek and return results quickly. Later, SQL Server might choose a different execution plan that reads more data or performs more expensive operations.

Without historical information, you may only see that the query is currently slow. Query Store lets you compare its current performance with earlier executions and investigate whether a plan change contributed to the problem.

Query Store is not a complete replacement for execution-plan analysis or other monitoring tools. It provides the historical information needed to make that investigation more effective.

What Is a Query Regression?

A query regression occurs when a query performs worse than it did previously.

For example, a query might previously take 100 milliseconds but later take 1,200 milliseconds under comparable conditions.

These numbers are illustrative, not benchmark results.

A regression may be caused by:

A different execution plan.
Changes in data volume or distribution.
Outdated or changed statistics.
Missing or unsuitable indexes.
Increased blocking or resource contention.
Changes in parameter values or workload patterns.

A different execution plan is an important possibility, but it is not the only explanation for a slow query.

The goal is to identify what changed and determine whether that change is responsible for the performance problem.

Step 1: Enable Query Store

Query Store can be enabled through SQL Server Management Studio or a SQL command.

First, check whether Query Store is enabled for the database.

SELECT
    name,
    is_query_store_on
FROM sys.databases
WHERE name = N'SalesDB';

Replace SalesDB with your database name.

If Query Store is disabled, you can enable it with the following command:

ALTER DATABASE SalesDB
SET QUERY_STORE = ON;

For a production database, you should also review Query Store's storage limits, capture mode, retention settings, and operation mode. Defaults and available options can vary by SQL Server version.

You can configure additional settings, for example:

ALTER DATABASE SalesDB
SET QUERY_STORE
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 1000
);

This configuration sets Query Store to read-write mode, retains historical data according to the configured cleanup policy, sets a data-flush interval, and defines a storage limit.

Choose these values according to the database workload and monitoring requirements. A short retention period may remove information you need to investigate older incidents, while an unnecessarily large storage limit consumes more database space.

Step 2: Find Queries with High Execution Time

Once Query Store has collected data, you can inspect query performance using its catalog views.

The following query lists statements with the highest average execution duration.

SELECT TOP (20)
    q.query_id,
    p.plan_id,
    SUM(rs.count_executions) AS TotalExecutions,
    AVG(rs.avg_duration) AS AvgDurationMicroseconds,
    MAX(rs.max_duration) AS MaxDurationMicroseconds
FROM sys.query_store_query AS q
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
GROUP BY
    q.query_id,
    p.plan_id
ORDER BY
    AVG(rs.avg_duration) DESC;

This query helps identify plans associated with statements that have high average execution duration.

The duration values exposed by Query Store runtime statistics are measured in microseconds, so divide them by 1,000 to convert them to milliseconds.

There is one important limitation: this query combines statistics across available runtime-statistics rows and intervals. It does not filter by a specific time window, and the results should not be treated as a direct comparison between yesterday and today.

For a more useful investigation, compare performance during specific intervals and consider execution counts, average duration, total duration, and the workload that was active at the time.

Step 3: Compare Query Performance Over Time

A query may have a high average duration because it has always been expensive. To find a regression, you need to compare its performance across different periods.

Query Store stores runtime statistics by interval. The following example shows the average duration of each query plan over time.

SELECT
    q.query_id,
    p.plan_id,
    rsi.start_time,
    rsi.end_time,
    SUM(rs.count_executions) AS TotalExecutions,
    AVG(rs.avg_duration) AS AvgDurationMicroseconds
FROM sys.query_store_query AS q
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(DAY, -2, SYSUTCDATETIME())
GROUP BY
    q.query_id,
    p.plan_id,
    rsi.start_time,
    rsi.end_time
ORDER BY
    q.query_id,
    rsi.start_time;

This query returns interval-level statistics for the previous two days, using UTC timestamps.

Compare the same query across intervals and look for a meaningful increase in average duration. Also check whether the execution count changed significantly.

For example, an increase in average duration may indicate a regression, while a large increase in total duration may simply reflect a higher request volume.

For more precise comparisons, use comparable workload periods and inspect the individual plans associated with the query.

Step 4: Find Queries That Use Multiple Execution Plans

One of the most useful Query Store features is its ability to retain information about different execution plans used by the same query.

A query may have one plan that performs well and another that performs poorly.

Use the following query to find statements with multiple stored plans.

SELECT
    q.query_id,
    COUNT(DISTINCT p.plan_id) AS PlanCount
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
GROUP BY
    q.query_id
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY
    PlanCount DESC;

Having multiple plans does not automatically mean there is a problem. SQL Server may legitimately use different plans for different parameter values or data conditions.

However, if a query became slower after switching plans, comparing those plans is a useful next step.

You can inspect the available plans in SQL Server Management Studio by opening Query Store reports for the database and reviewing the query's plan history.

Step 5: Investigate a Regressed Execution Plan

Once you have identified a query that has become slower, compare its execution plans.

Look for differences such as:

An index seek being replaced by a scan.
A large increase in estimated or actual rows processed.
Expensive sorts or hash operations.
Increased key lookups.
Differences in join strategies.
Missing or unsuitable indexes.

An index scan is not automatically bad, and an index seek is not automatically good. The right plan depends on the query, the data, and how many rows must be processed.

If the plan change appears to be responsible for the regression, Query Store can help you test a more stable plan choice.

For example, you can inspect plans associated with a query using the following SQL:

SELECT
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    p.last_execution_time
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
WHERE q.query_id = 123;

Replace 123 with the query ID you are investigating.

This shows the stored plans and whether they are currently forced. Review the actual plans and runtime statistics before deciding whether to force one.

Step 6: Force a Better Plan When Appropriate

If your investigation confirms that a particular plan performs better and the regression is caused by a plan choice, Query Store can force that plan.

EXEC sys.sp_query_store_force_plan
    @query_id = 123,
    @plan_id = 456;

Replace the query and plan IDs with the values you have verified.

Plan forcing tells SQL Server to try to use the selected plan for that query. It does not guarantee that the plan will always be usable, nor does it fix problems caused by every possible performance issue.

Before forcing a plan, consider whether the workload or data distribution has changed. A plan that worked well for yesterday's data may not be the best option for tomorrow's workload.

Monitor the query after forcing the plan and remove the forcing if it causes problems.

You can remove a forced plan with:

EXEC sys.sp_query_store_unforce_plan
    @query_id = 123,
    @plan_id = 456;

Plan forcing should be treated as a controlled tuning decision, not a substitute for understanding the underlying problem.

Best Practices for Production Databases

1. Keep enough history to investigate incidents

Configure retention and storage limits so Query Store retains the data you need. Check that cleanup policies are not removing useful historical information too quickly.

2. Compare similar workloads

Compare query performance during similar time periods and under comparable workloads. Different parameter values, request volumes, and data distributions can affect execution time.

3. Inspect the execution plan

Use Query Store to find the relevant plan, then examine its operators, estimated row counts, indexes, and joins. Where appropriate, compare it with an actual execution plan collected during a controlled investigation.

4. Avoid forcing plans without evidence

Plan forcing can help with a confirmed plan regression, but forcing a plan without understanding the query can create a different performance problem.

5. Monitor resource usage

Query Store helps explain query behavior, but CPU pressure, blocking, memory pressure, and storage latency may also affect performance. Consider those factors before attributing every slowdown to an execution-plan change.

Common Mistakes and Troubleshooting

Problem	Possible reason	Recommended action
No query history appears	Query Store is disabled or not capturing the relevant queries	Check its operation mode and capture settings
A slow query does not appear in the results	Capture filters or time-window filters exclude it	Review Query Store settings and reporting intervals
A query has multiple plans	Different parameter values or changing data conditions	Compare plan performance before taking action
A forced plan does not solve the problem	The cause is not the execution plan alone	Investigate blocking, indexes, statistics, and resource pressure
Historical data is missing	Retention or storage limits removed older data	Review cleanup and storage policies
Average duration looks misleading	Different workloads or execution counts are mixed together	Compare interval-level statistics and execution counts

One common mistake is assuming that the slowest query by average duration is always the most important problem. A query that takes a long time but runs once a day may have less impact than a moderately slow query executed thousands of times.

Consider both duration and frequency when deciding what to investigate first.

Advantages and Disadvantages of Query Store

Advantages

Maintains query and execution-plan history.
Helps identify changes in query performance.
Provides runtime statistics for comparison over time.
Supports investigation of plan regressions.
Allows plan forcing when a verified plan change needs a controlled workaround.

Disadvantages

Uses database storage and adds some monitoring overhead.
Requires suitable retention and capture settings.
Does not automatically explain every cause of slow execution.
Plan forcing can become harmful if workload conditions change.
Historical statistics may need careful interpretation when workloads differ.

Summary

SQL Server Query Store is a valuable tool for investigating queries that suddenly become slow. It records query text, execution plans, and runtime statistics so you can compare behavior over time instead of relying only on the current execution.

Start by enabling Query Store and identifying the queries with the greatest performance impact. Compare their runtime statistics across intervals, inspect plan changes, and investigate other possible causes such as indexing, statistics, blocking, and resource pressure.

If a confirmed execution-plan regression is responsible, plan forcing can provide a controlled workaround while you investigate a longer-term solution.

The most effective approach is to use Query Store as evidence for troubleshooting, then validate any tuning change against the actual workload.