If you are still troubleshooting performance using only execution plans and DMVs, you are working the hard way.
Query Store is like a black box flight recorder for your database.
It captures:
✔ Query history
✔ Execution plans
✔ Performance statistics
✔ Plan changes over time
This makes performance troubleshooting dramatically easier.
1️⃣ What is Query Store?
Query Store is a built-in feature introduced in Microsoft SQL Server 2016.
It automatically stores:
- Query text
- Execution plans
- Runtime statistics
- Historical performance data
📌 Even after SQL Server restart.
2️⃣ Why Query Store is Powerful
Without Query Store:
- You troubleshoot only live queries.
- Historical performance is lost.
With Query Store:
- You compare past vs current performance.
- You detect plan regressions.
- You force stable plans if needed.
3️⃣ Enabling Query Store
ALTER DATABASE MyDatabase
SET QUERY_STORE = ON;
Recommended settings:
ALTER DATABASE MyDatabase
SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900
);
📌 Always enable it in production databases.
4️⃣ Viewing Query Store Data
Using SSMS GUI:
Database → Query Store → Top Resource Consuming Queries
Or use query:
SELECT *
FROM sys.query_store_query;
5️⃣ Detecting Plan Regression
What is Plan Regression?
When:
- Same query
- New execution plan
- Suddenly becomes slow
Query Store lets you compare:
✔ Old plan
✔ New plan
✔ Performance differences
6️⃣ Forcing a Stable Plan
If a new plan is bad:
EXEC sp_query_store_force_plan
@query_id = 10,
@plan_id = 5;
✔ Forces known good plan
✔ Quick production fix
⚠ Use carefully. Always test first.
7️⃣ Finding Top CPU Consuming Queries
SELECT TOP 10
qsqt.query_sql_text,
qsr.avg_cpu_time,
qsr.avg_duration
FROM sys.query_store_query_text qsqt
JOIN sys.query_store_query qsq
ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsr
ON qsp.plan_id = qsr.plan_id
ORDER BY qsr.avg_cpu_time DESC;
📌 Perfect for performance tuning.
8️⃣ Identifying Frequently Failing Queries
Query Store tracks execution stats like:
- Count
- Duration
- Resource usage
You can identify:
✔ Queries running too often
✔ Queries consuming too much IO
9️⃣ Query Store vs DMVs
| Feature | DMVs | Query Store |
|---|---|---|
| Historical Data | ❌ Lost after restart | ✔ Saved |
| Plan History | ❌ No | ✔ Yes |
| Plan Forcing | ❌ No | ✔ Yes |
📌 Query Store is superior for long-term analysis.
🔟 Real Production Scenario
❌ Issue
After index rebuild, application became slow.
🔍 Root Cause
New execution plan generated.
✅ Fix
- Compared old vs new plan in Query Store
- Forced previous stable plan
- Performance restored immediately
1️⃣1️⃣ Best Practices for Query Store
✔ Enable on all production databases
✔ Monitor storage size
✔ Clean up old data
✔ Avoid forcing too many plans
✔ Review top resource-consuming queries weekly
1️⃣2️⃣ When NOT to Force Plans
❌ When data distribution changes
❌ When index structure changes
❌ When schema changes
📌 Forcing wrong plan can worsen performance.
1️⃣3️⃣ Monitoring Query Store Size
SELECT current_storage_size_mb,
max_storage_size_mb
FROM sys.database_query_store_options;
1️⃣4️⃣ Query Store Configuration Checklist
✔ Enabled
✔ READ_WRITE mode
✔ Automatic cleanup
✔ Regular monitoring
✔ Plan forcing only when required
✍️ Conclusion
Query Store turns performance tuning from guessing into data-driven decision making.
If you master Query Store, you become:
✔ Faster at troubleshooting
✔ More confident in production
✔ A performance specialist