Prerequisites: Understanding of execution plans. See DB 05 Performance Tuning.

Query Store is your flight recorder for SQL Server queries - it captures query history, plans, and performance metrics over time.

Part A: Query Store Basics

1. What is Query Store?

graph LR
    Q[Query Executes] --> QS[Query Store Captures]
    QS --> TEXT[Query Text]
    QS --> PLAN[Execution Plans]
    QS --> STATS[Runtime Statistics]
    
    style QS fill:#27ae60,color:#fff

Before Query Store:

• “This query was fast last week, what changed?” → No easy answer

With Query Store:

• Every query, every plan, every metric → stored and queryable

2. What Query Store Captures

3. Enabling Query Store

-- Enable Query Store
ALTER DATABASE MyDatabase
SET QUERY_STORE = ON;

-- Configure settings
ALTER DATABASE MyDatabase
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO
);

-- Check status
SELECT * FROM sys.database_query_store_options;

4. Query Store Settings Explained

Common Trap: READ_ONLY Mode

When MAX_STORAGE_SIZE_MB fills up, Query Store automatically switches to READ_ONLY mode and stops collecting new data!

If Query Store has no recent data, check this first:

SELECT actual_state_desc, readonly_reason 
FROM sys.database_query_store_options;

If it shows READ_ONLY, increase MAX_STORAGE_SIZE_MB or clean up old data.

Part B: Query Store Views

5. Built-in Reports (SSMS)

6. Key System Views

-- All stored queries
SELECT * FROM sys.query_store_query;

-- Query text
SELECT * FROM sys.query_store_query_text;

-- Execution plans
SELECT * FROM sys.query_store_plan;

-- Runtime statistics (aggregated)
SELECT * FROM sys.query_store_runtime_stats;

-- Wait statistics
SELECT * FROM sys.query_store_wait_stats;

Wait Stats Advantage: Per-Query Granularity

sys.query_store_wait_stats provides wait statistics at the individual query level, more precise than the traditional server-wide sys.dm_os_wait_stats.

It directly tells you: “What exactly is THIS query waiting on?” (Lock, I/O, or CPU)

7. Useful Queries

Find Top Resource Consumers

SELECT TOP 20
    qt.query_sql_text,
    q.query_id,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
    rs.avg_logical_io_reads,
    rs.count_executions,
    p.plan_id
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.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;

Find Queries with Multiple Plans

SELECT 
    q.query_id,
    qt.query_sql_text,
    COUNT(p.plan_id) AS plan_count
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
GROUP BY q.query_id, qt.query_sql_text
HAVING COUNT(p.plan_id) > 1
ORDER BY plan_count DESC;

Find Regressed Queries

-- Compare recent vs historical performance
SELECT 
    q.query_id,
    qt.query_sql_text,
    rs_recent.avg_duration / 1000.0 AS recent_duration_ms,
    rs_old.avg_duration / 1000.0 AS old_duration_ms,
    (rs_recent.avg_duration - rs_old.avg_duration) / rs_old.avg_duration * 100 AS pct_change
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
-- Recent stats (last 24 hours)
JOIN sys.query_store_runtime_stats rs_recent ON p.plan_id = rs_recent.plan_id
JOIN sys.query_store_runtime_stats_interval rsi_recent 
    ON rs_recent.runtime_stats_interval_id = rsi_recent.runtime_stats_interval_id
    AND rsi_recent.start_time > DATEADD(HOUR, -24, GETUTCDATE())
-- Old stats (7-14 days ago)
JOIN sys.query_store_runtime_stats rs_old ON p.plan_id = rs_old.plan_id
JOIN sys.query_store_runtime_stats_interval rsi_old 
    ON rs_old.runtime_stats_interval_id = rsi_old.runtime_stats_interval_id
    AND rsi_old.start_time BETWEEN DATEADD(DAY, -14, GETUTCDATE()) AND DATEADD(DAY, -7, GETUTCDATE())
WHERE rs_recent.avg_duration > rs_old.avg_duration * 2  -- 2x slower
ORDER BY pct_change DESC;

Part C: Plan Forcing

8. What is Plan Forcing?

When a query starts using a bad plan, force it to use a known good plan.

sequenceDiagram
    participant Q as Query
    participant QO as Query Optimizer
    participant QS as Query Store
    
    Note over Q,QS: Normal execution
    Q->>QO: Execute query
    QO->>Q: Use Plan B (bad!)
    
    Note over Q,QS: After forcing Plan A
    Q->>QO: Execute query
    QO->>QS: Check forced plans
    QS-->>QO: Use Plan A (forced)
    QO->>Q: Use Plan A (good!)

9. Forcing a Plan

-- Force a specific plan
EXEC sp_query_store_force_plan 
    @query_id = 42, 
    @plan_id = 101;

-- Unforce a plan
EXEC sp_query_store_unforce_plan 
    @query_id = 42, 
    @plan_id = 101;

-- See all forced plans
SELECT 
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    qt.query_sql_text
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE p.is_forced_plan = 1;

10. When to Force Plans

Warning: Plan forcing is a bandage, not a cure. Always investigate why the plan changed.

Schema Change Risk

If Table Schema changes significantly (e.g., an Index used by the forced plan is dropped), the forced plan may:

• Automatically become invalid (is_forced_plan = 0)
• Cause the query to fail

Regularly check for plans with force_failure_count > 0 in sys.query_store_plan.

Part D: Automatic Tuning

11. Automatic Plan Correction

SQL Server can automatically force better plans when regressions are detected.

-- Enable automatic tuning
ALTER DATABASE MyDatabase
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

-- Check automatic tuning status
SELECT * FROM sys.database_automatic_tuning_options;

-- See recommendations
SELECT 
    reason,
    state_desc,
    script,
    details
FROM sys.dm_db_tuning_recommendations;

12. How Automatic Tuning Works

sequenceDiagram
    participant SQL as SQL Server
    participant QS as Query Store
    participant AT as Automatic Tuning
    
    SQL->>QS: Query runs slower
    QS->>AT: Notify regression
    AT->>QS: Find previous good plan
    AT->>SQL: Force good plan
    AT->>AT: Monitor improvement
    
    alt Performance improved
        AT->>AT: Keep forcing
    else No improvement
        AT->>SQL: Unforce plan
    end

Part E: Troubleshooting with Query Store

13. Common Scenarios

Scenario 1: Query Got Slow After Weekend

-- Compare performance before/after
SELECT 
    rsi.start_time,
    rs.avg_duration / 1000.0 AS avg_ms,
    rs.count_executions,
    p.plan_id
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_runtime_stats_interval rsi 
    ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
WHERE q.query_id = @your_query_id
ORDER BY rsi.start_time;

Scenario 2: Different Plan on Different Servers

-- Get plan_id from both servers, compare in Query Store reports
SELECT 
    plan_id,
    query_plan,
    compatibility_level
FROM sys.query_store_plan
WHERE query_id = @your_query_id;

Scenario 3: Parameter Sniffing Investigation

-- See different executions with different stats
SELECT 
    rs.avg_duration / 1000.0 AS avg_ms,
    rs.min_duration / 1000.0 AS min_ms,
    rs.max_duration / 1000.0 AS max_ms,
    rs.stdev_duration / 1000.0 AS stdev_ms,
    rs.count_executions
FROM sys.query_store_runtime_stats rs
WHERE rs.plan_id = @your_plan_id;

-- High standard deviation = inconsistent = possibly parameter sniffing

14. Query Store Maintenance

-- Clear all Query Store data (use carefully!)
ALTER DATABASE MyDatabase SET QUERY_STORE CLEAR;

-- Flush to disk immediately
EXEC sp_query_store_flush_db;

-- Check space usage
SELECT 
    current_storage_size_mb,
    max_storage_size_mb,
    (current_storage_size_mb * 100.0 / max_storage_size_mb) AS pct_used,
    readonly_reason
FROM sys.database_query_store_options;

Summary

Query Store Quick Reference

Key Benefits

1. Historical analysis → Compare performance over time
2. Regression detection → Find queries that got slower
3. Plan stability → Force known-good plans
4. Automatic tuning → Let SQL Server fix regressions

Best Practices

1. Enable on all databases → Storage cost is minimal
2. Set appropriate retention → 30 days is usually enough
3. Monitor storage usage → Don’t let it fill up
4. Use AUTO capture mode → Avoid capturing everything
5. Investigate before forcing → Understand why plans changed

Practice Questions

Conceptual

1. What data does Query Store capture and why is it useful?

2. What is plan forcing and when should you use it?

3. Explain how Automatic Tuning works with Query Store.

Hands-on

-- Write a query to find the top 10 most resource-intensive queries
-- in the last 24 hours, showing query text, avg duration, and execution count.

SELECT TOP 10
    qt.query_sql_text,
    q.query_id,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.count_executions
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.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
JOIN sys.query_store_runtime_stats_interval rsi 
    ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.start_time > DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

Scenario

1. Troubleshooting: A critical stored procedure was fast (100ms) on Friday but became slow (5 seconds) on Monday. How would you use Query Store to diagnose and fix this?