SQL Server Query Store: How It Works and How to Force a Better Plan

The plan cache (Module 1) resets on restart and only shows current state. Query Store persists query and plan performance history per database, across restarts — and lets you directly force a known-good plan.

Enabling It

ALTER DATABASE YourDatabase SET QUERY_STORE = ON;
ALTER DATABASE YourDatabase SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Finding Regressed Queries

SELECT q.query_id, qt.query_sql_text, rs.avg_duration, rs.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

The Feature That Directly Fixes Parameter Sniffing: Forcing a Plan

EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 137;
-- Later, to release it:
EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 137;

This is a genuinely production-safe response to the Module 3 parameter sniffing problem — no code deployment needed, immediately reversible, and the exact plan is verifiable (unlike a hint that only influences future compilation).