Stabilize SQL Server Query Performance with Plan Forcing

Problem and takeaway

A critical report runs fine for weeks, then suddenly takes minutes instead of seconds. The query text did not change, but SQL Server chose a different execution plan—often because of parameter sniffing, stale statistics, or a new index. Users notice the regression before you do.

Query Store plan forcing lets you pin a specific execution plan for a problem query so SQL Server keeps using it. This is a targeted operational override, not a rewrite. It works in SQL Server 2016 and later, and it can buy you time to fix root causes such as missing indexes or outdated statistics.

Enable Query Store and check storage

Query Store captures query text, runtime statistics, wait stats, and execution plans inside the database. It runs in read-write, read-only, or off mode. Before forcing anything, confirm it is active and has room to grow.

Run this in the target database context. You need ALTER DATABASE permission or db_owner membership.

SELECT is_enabled, actual_storage_mb, desired_storage_mb, read_reason
FROM sys.database_query_store_options
WHERE database_id = DB_ID();

If is_enabled is 0, enable it with a storage quota sized for your workload. Adjust MAX_STORAGE_SIZE_MB based on query volume and retention needs.

ALTER DATABASE CURRENT
SET QUERY_STORE (
    READ_WRITE = ON,
    MAX_STORAGE_SIZE_MB = 1024,
    RETENTION_PERIOD_DAYS = 30,
    QUERY_CAPTURE_MODE = AUTO
);

This command changes database state. Run it during a maintenance window or low-traffic period, and monitor actual_storage_mb afterward so the store does not consume unexpected disk space.

Find the regressed query and inspect its plans

Identify the query by query_id, then compare the captured plans. This example returns the ten queries with the highest total elapsed time in the last day.

SELECT TOP 10
    q.query_id,
    qt.query_sql_text,
    rs.avg_duration,
    rs.execution_count,
    rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_runtime_stats AS rs ON q.query_id = rs.query_id
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, -1, SYSUTCDATETIME())
ORDER BY rs.avg_duration DESC;

Pick the query_id showing the regression. For illustration, assume query_id = 42. Inspect its plans to find the one you want to lock.

SELECT plan_id, is_forced, last_execution_time, execution_count
FROM sys.query_store_plan
WHERE query_id = 42
ORDER BY plan_id;

Assume plan_id = 7 is the stable plan you want to keep. Verify its recent performance before forcing it. Compare elapsed time and CPU against the regressed plan using sys.query_store_runtime_stats or an actual execution plan captured with sys.dm_exec_query_plan.

Force the plan and verify it stayed forced

To force the plan, run sp_query_store_force_plan. Replace the placeholders with your actual query_id and plan_id. You need ALTER ANY CONNECTION or db_owner rights.

EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;

This changes database state by marking plan_id 7 as forced for query_id 42. Confirm the force took effect:

SELECT query_id, plan_id, is_forced
FROM sys.query_store_plan
WHERE query_id = 42 AND plan_id = 7;

Look for is_forced = 1. Then run the query again and compare elapsed time, CPU, and the actual execution plan against the baseline. Use SET STATISTICS TIME ON or Extended Events to measure, and confirm the plan handle matches the forced plan.

Trade-offs, rollback, and when to revisit

Forced plans do not adapt automatically. If schema changes, data distribution shifts, or a better index is added later, the locked plan can become suboptimal. Query Store also adds storage overhead proportional to captured plans and retention period.

If performance degrades after forcing, roll back the override with sp_query_store_unforce_plan. This also changes database state.

EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 7;

Check is_forced returns 0 afterward. Schedule a review of forced plans after any major schema change, statistics update, or index modification. Treat plan forcing as a stabilizer while you address the underlying issue, not as a permanent substitute for good indexing and statistics maintenance.

Actionable closing

• Enable Query Store with a storage limit and retention period matched to your workload.
• Use runtime statistics to find regressed queries and note their query_id values.
• Validate the candidate plan’s performance before forcing it.
• Force the plan, verify is_forced = 1, and compare runtime metrics.
• Document the query_id and plan_id, then unforce or revisit after schema changes.