Query Store: Configure and Monitor
Configure Query Store to capture query performance history. Use Query Store reports to identify regressions, plan changes, and resource-heavy queries.
Query Store fundamentals
Query Store is your database’s flight recorder.
Just like a plane’s black box records everything during a flight, Query Store records every query’s execution plan, duration, CPU usage, and I/O — over days, weeks, or months. When a query suddenly gets slow, you can compare “before” and “after” to see exactly what changed.
Best of all, you can force the database to use an older, faster plan — like telling the pilot to go back to the route that worked.
Configuring Query Store
Default state by platform
| Platform | Default State | Notes |
|---|---|---|
| Azure SQL Database | ON (Read Write) | Enabled automatically on all new databases |
| Azure SQL Managed Instance | ON (Read Write) | Enabled automatically |
| SQL Server on VMs | OFF | Must be enabled manually |
Configuration options
-- Enable Query Store (SQL Server on VMs)
ALTER DATABASE NorthStarERP SET QUERY_STORE = ON;
-- Configure settings
ALTER DATABASE NorthStarERP SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
MAX_STORAGE_SIZE_MB = 1024,
DATA_FLUSH_INTERVAL_SECONDS = 900,
INTERVAL_LENGTH_MINUTES = 60,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
SIZE_BASED_CLEANUP_MODE = AUTO,
QUERY_CAPTURE_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200,
WAIT_STATS_CAPTURE_MODE = ON
);Key settings explained
| Setting | What It Controls | Recommended |
|---|---|---|
| OPERATION_MODE | READ_WRITE (active) or READ_ONLY | READ_WRITE for production |
| MAX_STORAGE_SIZE_MB | Maximum space for Query Store data | 1-2 GB for most workloads |
| CLEANUP_POLICY | How long to keep data (STALE_QUERY_THRESHOLD_DAYS) | 30 days (default) |
| QUERY_CAPTURE_MODE | Which queries to capture: ALL, AUTO, CUSTOM, NONE | AUTO (skips trivial queries) |
| WAIT_STATS_CAPTURE_MODE | Capture per-query wait statistics | ON |
| SIZE_BASED_CLEANUP_MODE | Auto-purge oldest data when max size reached | AUTO |
QUERY_CAPTURE_MODE options
- ALL: Captures every query. High overhead, useful for thorough analysis.
- AUTO: Captures queries that exceed resource thresholds (recommended). Skips trivial queries like single lookups.
- CUSTOM: Fine-tuned with EXECUTION_COUNT and TOTAL_COMPILE_CPU_TIME_MS thresholds.
- NONE: Query Store is on but not capturing (read-only for existing data).
The exam often tests the difference between ALL and AUTO — AUTO is the recommended production setting.
Monitoring with Query Store
Built-in reports (SSMS)
| Report | What It Shows | Use For |
|---|---|---|
| Regressed Queries | Queries whose performance degraded over time | Identifying plan regressions |
| Top Resource Consuming Queries | Queries using the most CPU, duration, I/O, or memory | Finding optimization candidates |
| Overall Resource Consumption | Database-wide resource trends | Capacity planning |
| Tracked Queries | Performance history for a specific query | Before/after comparison |
| Queries With Forced Plans | Queries where you’ve forced a specific plan | Managing forced plans |
| Queries With High Variation | Queries with inconsistent performance | Identifying parameter-sensitive plans |
Identifying plan regression
Tomas’s scenario: A sales report that used to run in 2 seconds now takes 45 seconds.
- Open Regressed Queries report in SSMS
- Find the query — it shows two execution plans
- Plan A (old): Index Seek + Nested Loop — fast
- Plan B (new): Clustered Index Scan + Hash Join — slow
- Compare: Plan B was chosen after a statistics update changed cardinality estimates
- Force Plan A — the optimizer will use the fast plan
-- Force a specific plan
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;
-- Unforce a plan
EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 7;
-- View forced plans
SELECT query_id, plan_id, force_failure_count
FROM sys.query_store_plan
WHERE is_forced_plan = 1;Wait statistics per query
Query Store captures wait stats per query — essential for understanding WHY a query is slow:
-- Top queries by total wait time
SELECT TOP 10
q.query_id,
qt.query_sql_text,
ws.wait_category_desc,
ws.avg_query_wait_time_ms
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_wait_stats ws ON q.query_id = ws.query_id
ORDER BY ws.avg_query_wait_time_ms DESC;Query Store best practices
| Practice | Why |
|---|---|
| Keep OPERATION_MODE = READ_WRITE | Active collection is the default and recommended |
| Use AUTO capture mode | Avoids overhead from trivial queries |
| Monitor Query Store size | If it fills up, it switches to READ_ONLY automatically |
| Force plans cautiously | Forced plans may become suboptimal as data changes — review periodically |
| Don’t disable on Azure SQL DB | It’s enabled by default for a reason — monitoring without it is much harder |
| Use custom capture for OLTP | Fine-tune thresholds to skip high-frequency, low-cost queries |
A critical report query suddenly became 20x slower after a statistics update. Tomas finds two plans in Query Store — Plan A (fast, old) and Plan B (slow, new). What should he do first?
Priya wants Query Store to capture significant queries but skip trivial single-row lookups that execute thousands of times per second. Which capture mode should she use?
🎬 Video coming soon
Next up: Blocking, DMVs, and Execution Plans — diagnose session blocking, use DMVs for performance analysis, and read execution plans.