Query Store is the single most underused feature in SQL Server. It records every query, every plan, every execution, with statistics, and lets you force a known-good plan when the optimizer picks a bad one. Turn it on, size it correctly, and you'll never miss Profiler again.
Query Store shipped with SQL Server 2016 and has improved with every release since. It is on by default in Azure SQL Database and SQL Server 2022. On older boxed editions you have to turn it on — and most teams either never do, or turn it on with default settings that fill the store in a week.
This is the practical playbook.
What Query Store gives you
- Every query that runs, normalized and stored with its plan(s)
- Per-plan runtime statistics: CPU, duration, logical reads, physical reads, memory grants, parallelism
- A built-in UI in SSMS for the most common views (regressed queries, top resource consumers, queries with high variance)
- The ability to force a known-good plan so the optimizer stops picking the bad one
- A retention model with size limits so it doesn't grow forever
Turning it on — with sane settings
ALTER DATABASE YourDB SET QUERY_STORE = ON
(OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = AUTO,
MAX_STORAGE_SIZE_MB = 4096,
INTERVAL_LENGTH_MINUTES = 30,
STALE_QUERY_THRESHOLD_DAYS = 90,
SIZE_BASED_CLEANUP_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200);Why these values:
- MAX_STORAGE_SIZE_MB: 4 GB is enough for most OLTP. Larger for high-velocity systems. Default is 100 MB — way too small for any real workload.
- INTERVAL_LENGTH_MINUTES: 30 minutes is a good balance between fine-grained insight and storage cost.
- QUERY_CAPTURE_MODE = AUTO: filters out trivial queries (default is ALL, which floods the store with low-value noise).
- STALE_QUERY_THRESHOLD_DAYS: 90 days is enough to see month-over-month regressions and post-deployment changes.
The reports you'll use most
Regressed queries
Queries whose performance got worse after a plan change. This is the killer feature — after a deployment or a stats update, this view tells you exactly which queries got faster, which got slower, and by how much.
Top resource consumers
Sort by CPU or duration to find the queries that consume the most resource overall. This is your "where to spend tuning time" list.
Queries with high variation
Same query, very different execution times. This is the parameter sniffing finder. Parameter sniffing fix playbook here.
Tracked queries
Pin a specific query and watch every plan it produces over time. Indispensable when chasing a regression.
Forcing a plan
When you've confirmed plan A is consistently better than plan B for a given query, force plan A:
EXEC sp_query_store_force_plan
@query_id = 12345,
@plan_id = 67890;SQL Server will use plan A for that query until you unforce it or the plan becomes invalid (schema change, dropped index). It's safer than a query hint because the hint isn't compiled into the procedure; it's a separate, removable directive.
What it doesn't replace
- Wait stats analysis — Query Store tells you which queries cost the most. Wait stats tell you why.
- Extended Events — for ad-hoc deep dives into events Query Store doesn't capture (lock escalation, deadlock graphs, log shipping internals)
- Real-time troubleshooting — Query Store data lags by up to the interval length; for live blocking use
sp_WhoIsActive
Common mistakes
Leaving the default size
The 100 MB default fills in days on a busy OLTP system, then Query Store flips to READ_ONLY and silently stops capturing.
Using QUERY_CAPTURE_MODE = ALL
Floods the store with ad-hoc queries. AUTO filters appropriately.
Forgetting to monitor it
SELECT desired_state_desc, actual_state_desc,
readonly_reason, current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;If actual_state_desc shows READ_ONLY and desired_state_desc shows READ_WRITE, the store filled up. Increase MAX_STORAGE_SIZE_MB.
Forcing too many plans
Plan forcing is a hammer. Use it for known regressions; don't force every plan that ever looked good. Schema changes can invalidate forced plans and the engine will silently fall back — review forced plans quarterly.
Query Store on read-only secondaries
In SQL Server 2022, you can finally enable Query Store on Always On read-only secondaries — invaluable for tuning reporting workloads that hit the secondary. Before 2022, secondary-only workloads were a black box.
Not using Query Store yet? A SQL Server health check includes Query Store setup, sizing, and a walk-through of the top regressed and high-variance queries in your environment. Book below.