Using Query Store to Pin a Good Plan and Stop Performance Regressions in SQL Server

Problem: Sudden Slowdown After a Statistics Update

\n

After a routine statistics refresh, a reporting stored procedure that usually runs in under a second begins to take several seconds. Users notice longer page loads and the application’s throughput drops. The change is not due to schema modifications or blocking; the query text is identical, but its execution plan has shifted to a less efficient one.

\n\n

Thesis: Use Query Store to Capture, Diagnose, and Force a Good Plan

\n

Query Store persists query text, execution plans, runtime statistics, and wait metrics inside the database. By enabling it, you can compare the current plan with a previously known‑good plan and, if needed, force the optimizer to reuse that plan. This gives a reversible way to stop a regression without changing application code.

\n\n

Enabling Query Store

\n

To turn on Query Store you need ALTER ANY DATABASE permission (or be a member of the db_owner role). The following T‑SQL enables it with a moderate storage budget and automatic capture:

\n

-- Execute in the context of the target database\nALTER DATABASE [YourDB] SET QUERY_STORE = ON\n    (OPERATION_MODE = READ_WRITE,\n     CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),\n     DATA_FLUSH_INTERVAL_SECONDS = 900,\n     MAX_STORAGE_SIZE_MB = 500,\n     INTERVAL_LENGTH_MINUTES = 60,\n     QUERY_CAPTURE_MODE = AUTO);\n

\n

After running the command, verify the setting in SSMS: right‑click the database → Properties → Query Store. The Operation Mode should read ReadWrite and the Max Storage Size should show 500 MB.

\n\n

Finding a Regressed Query

\n

Open SQL Server Management Studio, right‑click the database, choose Reports → Standard Reports → Query Store Regressions. The report lists queries whose latest execution plan has a higher cost compared to the previous plan, showing the plan ID, average duration, and the percentage regression.

\n\n

Worked Example: Forcing a Plan

\n

Suppose the Regressions report highlights query ID 42. The current plan (plan_hash 0x3C4D) has an average duration of 2.8 s, while the earlier good plan (plan_hash 0x1A2B) averaged 0.4 s.

\n

\n
1. In the Regressions report, select the row for query ID 42 and click the **Force Plan** button.
\n
2. A dialog appears; confirm that you want to force plan 0x1A2B for this query.
\n
3. SSMS updates the Query Store; the forced plan is now marked as “Forced” in the Tracked Queries view.
\n
4. Run a representative workload, for example:
\n

\n

DECLARE @i int = 0;\nWHILE @i < 20\nBEGIN\n    EXEC dbo.GetOrders @CustomerID = 5;\n    SET @i = @i + 1;\nEND\n

\n

After the workload completes, reopen the Regressions report. Query ID 42 should no longer appear, indicating the forced plan is being used and the regression has disappeared.

\n

Note: The above steps are based on Microsoft’s documentation; they have not been executed in this example.

\n\n

Trade‑off: Overhead and Masking Issues

\n

Every statement executed while Query Store is in READ_WRITE mode adds a small amount of CPU and I/O to write the runtime data. In high‑frequency OLTP environments you can reduce the impact by:

\n

\n
• Setting QUERY_CAPTURE_MODE to AUTO or CUSTOM to limit which queries are captured.
\n
• Increasing DATA_FLUSH_INTERVAL_SECONDS so writes occur less often.
\n
• Choosing a lower MAX_STORAGE_SIZE_MB if retention of older data is not required.
\n

\n

Plan forcing can hide the root cause of a regression, such as parameter sniffing or outdated statistics. Treat a forced plan as a temporary mitigation; schedule a review to update statistics, examine parameter sensitivity, or consider query redesign.

\n\n

Actionable Closing

\n

\n
1. Enable Query Store on databases where performance regressions are costly.
\n
2. Baseline your workload and periodically check the Regressions report.
\n
3. When a regression appears, force the known‑good plan as a quick fix.
\n
4. After forcing, monitor the query’s statistics and plan to ensure the forced plan remains optimal.
\n
5. Remove the force hint once the underlying issue (e.g., statistics update) is resolved, allowing the optimizer to choose the best plan again.
\n

\n

By treating Query Store as a monitoring and control plane, you gain visibility into plan changes and a reversible mechanism to keep performance stable.