Enabling and Using Query Store in SQL Server

As a SQL Server DBA, the Query Store is one of the most powerful tools for monitoring query performance, detecting regressions, and stabilizing execution plans. Below is a step-by-step guide to enabling Query Store and leveraging it with practical queries.

1. What is Query Store?

Query Store, introduced in SQL Server 2016, tracks query execution history, execution plans, and runtime statistics over time. It simplifies troubleshooting performance regressions and makes it possible to force stable plans.

Key benefits:

Captures query text, execution plans, and performance stats.
Detects performance regressions over time.
Allows DBAs to force stable plans.
Stores data inside the database (included in backups).

2. Enabling Query Store

Using SSMS (GUI):

In Object Explorer, right-click the database → Properties → Query Store.
Set Operation Mode = Read Write.
Configure recommended settings:
- Data Flush Interval (sec): 900
- Statistics Collection Interval (min): 15
- Max Size (MB): 1024 (adjust based on workload)
- Query Capture Mode: AUTO
- Stale Query Threshold (days): 30
- Wait Stats Capture: ON (SQL 2017+)

Using T-SQL:

ALTER DATABASE [YourDB] SET QUERY_STORE = ON;
ALTER DATABASE [YourDB] SET QUERY_STORE
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 15,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    WAIT_STATS_CAPTURE_MODE = ON
);

3. Verifying Query Store Status

SELECT
    DB_NAME() AS database_name,
    desired_state_desc,
    actual_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    interval_length_minutes,
    query_capture_mode_desc
FROM sys.database_query_store_options;

If Query Store becomes READ_ONLY due to space limits:

ALTER DATABASE [YourDB] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 4096);

4. Useful Queries from Query Store

Top CPU Consumers (last 24 hours)

SELECT TOP (25)
    (SUM(rs.total_cpu_time)/1000.0) AS total_cpu_ms,
    SUM(rs.execution_count) AS execs,
    (SUM(rs.total_cpu_time)/NULLIF(SUM(rs.execution_count),0))/1000.0 AS avg_cpu_ms,
    qt.query_sql_text
FROM sys.query_store_runtime_stats rs
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
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.end_time > DATEADD(hour, -24, SYSUTCDATETIME())
GROUP BY qt.query_sql_text
ORDER BY total_cpu_ms DESC;

Top Duration Queries

SELECT TOP (25)
    (SUM(rs.total_duration)/1000.0) AS total_duration_ms,
    SUM(rs.execution_count) AS execs,
    (SUM(rs.total_duration)/NULLIF(SUM(rs.execution_count),0))/1000.0 AS avg_duration_ms,
    qt.query_sql_text
FROM sys.query_store_runtime_stats rs
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
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.end_time > DATEADD(hour, -24, SYSUTCDATETIME())
GROUP BY qt.query_sql_text
ORDER BY total_duration_ms DESC;

Detecting Plan Regressions

SELECT TOP (25)
    q.query_id,
    (SUM(CASE WHEN rsi.end_time > DATEADD(hour, -24, SYSUTCDATETIME()) THEN rs.total_duration END)
     / NULLIF(SUM(CASE WHEN rsi.end_time > DATEADD(hour, -24, SYSUTCDATETIME()) THEN rs.execution_count END),0))/1000.0 AS now_avg_ms,
    (SUM(CASE WHEN rsi.end_time BETWEEN DATEADD(hour, -48, SYSUTCDATETIME()) AND DATEADD(hour, -24, SYSUTCDATETIME()) THEN rs.total_duration END)
     / NULLIF(SUM(CASE WHEN rsi.end_time BETWEEN DATEADD(hour, -48, SYSUTCDATETIME()) AND DATEADD(hour, -24, SYSUTCDATETIME()) THEN rs.execution_count END),0))/1000.0 AS prev_avg_ms,
    qt.query_sql_text
FROM sys.query_store_runtime_stats rs
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
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.end_time > DATEADD(hour, -48, SYSUTCDATETIME())
GROUP BY q.query_id, qt.query_sql_text
HAVING (SUM(CASE WHEN rsi.end_time > DATEADD(hour, -24, SYSUTCDATETIME()) THEN rs.total_duration END)
        / NULLIF(SUM(CASE WHEN rsi.end_time > DATEADD(hour, -24, SYSUTCDATETIME()) THEN rs.execution_count END),0)) >
       2 * (SUM(CASE WHEN rsi.end_time BETWEEN DATEADD(hour, -48, SYSUTCDATETIME()) AND DATEADD(hour, -24, SYSUTCDATETIME()) THEN rs.total_duration END)
        / NULLIF(SUM(CASE WHEN rsi.end_time BETWEEN DATEADD(hour, -48, SYSUTCDATETIME()) AND DATEADD(hour, -24, SYSUTCDATETIME()) THEN rs.execution_count END),0))
ORDER BY now_avg_ms DESC;

Queries with Multiple Plans (potential instability)

SELECT TOP (50)
    q.query_id,
    COUNT(*) AS plan_count,
    qt.query_sql_text
FROM sys.query_store_query q
JOIN sys.query_store_plan p  ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
GROUP BY q.query_id, qt.query_sql_text
HAVING COUNT(*) > 1
ORDER BY plan_count DESC;

5. Forcing and Removing Plans

-- Force a specific plan
EXEC sys.sp_query_store_force_plan @query_id = 12345, @plan_id = 67890;

-- Unforce the plan
EXEC sys.sp_query_store_unforce_plan @query_id = 12345, @plan_id = 67890;

6. Maintenance

Increase storage if Query Store flips to READ_ONLY:

ALTER DATABASE [YourDB] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 4096);

Remove specific entries:

EXEC sys.sp_query_store_remove_plan  @plan_id = 67890;
EXEC sys.sp_query_store_remove_query @query_id = 12345;

Clear all Query Store data:

ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR;

7. Real-World Use Cases

Troubleshooting Parameter Sniffing
When a query performs well with one parameter but poorly with another, Query Store helps detect multiple execution plans for the same query. You can force the best-performing plan until code fixes are deployed.
Post-Deployment Regressions
After application upgrades, queries can suddenly slow down. By comparing performance before and after the deployment using Query Store runtime stats, DBAs can pinpoint regressed queries quickly.
High-Variability Reports
Long-running reports that generate different execution plans depending on filters often cause unpredictable performance. Query Store makes it possible to identify plan instability and stabilize performance with forced plans.
Capacity Planning
DBAs can analyze CPU, duration, and I/O trends over time to anticipate workload growth and provide accurate hardware or scaling recommendations.
Performance Baseline Creation
Query Store maintains a historical record of query performance. This makes it ideal for creating performance baselines, useful during migrations, patching, or infrastructure changes.

8. Notes

SQL Server 2016+: Query Store is supported.
SQL Server 2022: Supports Query Store on readable secondaries and query hints.
Backups: Query Store data is included in database backups.

Final Thoughts

Query Store is a DBA’s black box flight recorder. Enabling it on critical databases provides visibility into performance history, query regressions, and plan changes. When combined with plan forcing, baselining, and proactive monitoring, it can save hours of troubleshooting and prevent production incidents.

Archives

Categories

Meta