A SQL query can perform well for weeks and then suddenly become slow without any changes to the application code. A database deployment, updated statistics, a schema change, or a different execution plan can alter how SQL Server processes the same query.

These performance regressions are difficult to investigate when developers only have access to the query's current execution plan. The plan that caused the problem might no longer be active, and the original performance measurements may be missing.

SQL Server Query Store helps solve this problem by retaining query text, execution plans, and runtime statistics over time. Developers and database administrators can compare historical performance, identify plan changes, and investigate whether a new execution plan is responsible for a slowdown.

This article explains how Query Store works, how to enable it, how to identify query regressions, and how to use the collected data to make safer performance improvements.

What Is SQL Server Query Store?

Query Store is a SQL Server database feature that captures information about query execution and stores it in the database. It records query-related metadata, execution plans, and aggregated runtime statistics so that developers can investigate performance across different time periods.

Without Query Store, troubleshooting often depends on currently cached execution plans, application logs, or manually collected performance data. Cached plans can disappear, and historical evidence may be unavailable when a problem is discovered.

Query Store provides a more persistent view of query behavior.

It is particularly useful when:

Query Store is available in supported SQL Server versions, but its default configuration and capabilities depend on the SQL Server release and database platform. Verify the feature support for the environment you are investigating.

Enable Query Store

Query Store is configured at the database level. Before enabling it, check whether it is already active and review its current settings.

Run the following query in the target database:

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

These columns help you determine whether Query Store is operating, whether it has entered a read-only state, how much storage it uses, and which query-capture mode is configured.

If Query Store is disabled, enable it:

ALTER DATABASE YourDatabase
SET QUERY_STORE = ON;

Replace YourDatabase with the actual database name. Run database-level configuration statements with an account that has the required permissions.

You can then configure its operation mode and retention settings:

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

These values are examples, not universal recommendations.

READ_WRITE allows Query Store to collect data. AUTO lets SQL Server apply its capture rules rather than recording every possible query indiscriminately. The cleanup policy controls how long stale data is retained, while the storage limit constrains Query Store's footprint.

Choose settings based on workload volume, storage availability, and the historical period required for investigations. If Query Store reaches its configured storage limit, data collection can be restricted until the condition is addressed.

Understand the Main Query Store Components

Query Store separates query information into related records.

Component

Purpose

Query text

Stores the SQL text associated with a query

Query

Identifies a query tracked by Query Store

Plan

Records an execution plan associated with a query

Runtime statistics

Stores aggregated execution measurements

Runtime statistics interval

Groups measurements into time intervals

Wait statistics

Provides categorized wait information when supported and enabled

A single query can have multiple execution plans over time. This is important because a regression may be caused not by the SQL text itself but by the optimizer selecting a different plan.

Query Store statistics are aggregated over intervals. They do not represent a detailed trace of every execution, and the statistics must be interpreted with the selected interval and execution count in mind.

Find Expensive Queries

Start by identifying queries with high execution duration.

The following example retrieves runtime statistics for a selected period:

SELECT TOP (20)
    q.query_id,
    p.plan_id,
    rs.count_executions,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.max_duration / 1000.0 AS max_duration_ms,
    rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
    rs.avg_logical_io_reads,
    rsi.start_time,
    rsi.end_time
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, -7, SYSUTCDATETIME())
ORDER BY rs.avg_duration DESC;

This query uses a seven-day window and ranks returned runtime-statistics rows by average duration.

There are several details to keep in mind.

First, Query Store duration values are reported in microseconds, which is why the example divides them by 1,000 to display milliseconds.

Second, the query returns statistics grouped by plan and interval. A query with multiple plans or intervals may appear multiple times.

Third, average duration alone does not tell the entire story. A query that takes 500 milliseconds once per hour may have a different business impact from a query that takes 100 milliseconds thousands of times per minute.

For a fuller investigation, consider execution counts, CPU time, logical reads, maximum duration, and the interval during which the regression appeared. For precise comparisons across changing workloads, aggregate the data carefully rather than treating every interval as equally representative.

Identify a Query Plan Regression

A common regression occurs when SQL Server switches from an efficient execution plan to a slower one.

Imagine that a query originally used an index seek and returned a small number of rows. After a statistics update or a change in data distribution, the optimizer chooses a plan involving a much larger scan or an expensive join.

The query text may remain unchanged, but execution time and resource consumption can increase.

Query Store helps identify this situation by showing the plans associated with a query and the runtime statistics recorded for each plan.

Compare the plans

You can use Query Store's built-in reports in SQL Server Management Studio to inspect queries with multiple plans and compare their performance over time.

The Regressed Queries report is particularly useful for discovering queries whose performance has deteriorated. The Top Resource Consuming Queries report can help identify queries consuming substantial CPU, duration, or I/O resources.

You can also retrieve plan information through Query Store's catalog views:

SELECT
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    p.last_compile_start_time,
    p.last_execution_time,
    p.avg_compile_duration,
    p.count_compiles
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
ORDER BY
    q.query_id,
    p.last_execution_time DESC;

The output identifies recorded plans and related metadata. The exact available columns depend on the SQL Server version.

To inspect the XML execution plan, retrieve the relevant query_plan value from sys.query_store_plan and open it in SQL Server Management Studio.

Look for meaningful differences, such as:

A scan is not automatically a problem, and an index seek is not automatically optimal. Evaluate the plan in the context of the query, data distribution, and measured resource usage.

Force a Known-Good Plan When Necessary

Query Store supports plan forcing for supported SQL Server environments. This can be useful when a specific query plan has demonstrated better performance than another recorded plan.

For example:

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

Replace the identifiers with the query and plan IDs identified during the investigation.

Plan forcing asks SQL Server to use the selected plan when possible. It does not guarantee that the plan can always be applied, and it should not be treated as a permanent substitute for understanding the regression.

Before forcing a plan:

  1. Confirm that the plan performed well under a representative workload.
  2. Compare resource usage and execution duration across relevant periods.
  3. Verify that the query's data distribution and workload have not changed in ways that make the old plan unsuitable.
  4. Test the proposed change when possible.
  5. Monitor the query after applying the plan.

To remove a forced plan when it is no longer appropriate, use:

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

Plan forcing should be a deliberate operational decision with a rollback path. An old plan that was efficient for yesterday's data may not be the best choice for tomorrow's workload.

Investigate Wait Statistics

A query can have a reasonable execution plan and still spend time waiting for resources.

Query Store can capture categorized wait statistics in supported SQL Server versions when the feature is enabled. These statistics help distinguish CPU-intensive work from time spent waiting on locks, storage, memory, or other resources.

For example, a query that suddenly slows down may be blocked by another transaction. In that case, forcing a different execution plan may not address the real problem.

Use the wait information alongside query duration, execution count, CPU time, and logical reads. If the evidence points to blocking, investigate transaction duration and lock acquisition patterns. If it points to I/O, examine the query's read volume and storage behavior.

Common Mistakes

Ranking queries only by average duration

Average duration can hide intermittent slow executions and high-frequency workloads. Review execution count, maximum duration, CPU usage, and logical reads as well.

Assuming every plan change is a regression

The optimizer may choose a different plan because the data distribution or workload has changed. A new plan can be better, worse, or simply different.

Compare actual performance measurements rather than judging a plan by its shape alone.

Keeping Query Store at an unsuitable capture level

A capture mode that is too restrictive may miss queries needed for an investigation. A mode that captures too much can increase storage consumption and operational overhead.

Choose capture and retention settings that match the database workload and troubleshooting requirements.

Forcing a plan without monitoring it

Plan forcing can provide a useful short-term mitigation, but the forced plan may become less effective as the data changes. Monitor the query and review the decision periodically.

Ignoring Query Store's storage state

If Query Store becomes read-only or stops capturing the expected data, historical investigations may be incomplete. Check its state and storage usage when the collected statistics do not match expectations.

Best Practices for Production Databases

  1. Enable Query Store before a performance incident occurs.
  2. Choose capture, retention, and storage settings based on workload volume.
  3. Compare the same query across equivalent time intervals whenever possible.
  4. Investigate plans alongside runtime statistics, wait information, and application metrics.
  5. Treat plan forcing as a controlled mitigation rather than an automatic fix.
  6. Test schema and index changes against representative workloads.
  7. Monitor Query Store storage consumption and operational state.
  8. Restrict access to query text and execution-plan information when they may contain sensitive business details.

Advantages and Disadvantages

Advantages

Disadvantages

Preserves query plans and performance history.

Consumes database storage and some processing resources.

Helps identify regressions after deployments or data changes.

Captured statistics are aggregated rather than a complete execution trace.

Supports comparison of multiple plans for the same query.

Interpreting plans requires SQL Server knowledge.

Provides reports for identifying resource-intensive queries.

Inappropriate capture and retention settings can limit usefulness.

Supports plan forcing as a possible mitigation.

A forced plan may become inefficient as workloads change.

Summary

SQL Server Query Store provides the historical evidence needed to investigate query performance regressions. By retaining query text, execution plans, and runtime statistics, it helps developers identify when a query changes behavior and whether a different plan may explain the slowdown.

Start by enabling Query Store, choosing suitable capture and retention settings, and examining the queries that consume the most duration, CPU, or logical reads. Compare plans and runtime statistics before making changes.

When a plan regression is confirmed, plan forcing can provide a controlled mitigation, but it should be monitored and reviewed. The most reliable approach combines Query Store evidence with execution-plan analysis, workload measurements, and an understanding of the database changes that preceded the regression.