SQL Server Query Store: Enable, Configure, and Avoid Common Pitfalls

The Problem: Missing Visibility into Query Performance

When a query suddenly runs slower, the execution plan may have changed. Without historical plan data, you're guessing. SQL Server Query Store solves this by persisting query text, execution plans, and runtime statistics (CPU, duration, I/O) inside the user database. It survives restarts, failovers, and even restores—if the Query Store data comes along.

Useful takeaway: Enable Query Store with a sensible configuration, monitor its storage footprint, and use the built-in catalog views to correlate queries with plans before you force anything.

How Query Store Works

Query Store writes to internal tables in the primary filegroup of each database where it's enabled. Data is flushed to disk asynchronously at a configurable interval (DATA_FLUSH_INTERVAL_SECONDS, default 900 seconds). Runtime statistics are aggregated into time windows defined by INTERVAL_LENGTH_MINUTES (default 60). This design keeps overhead low—typically 3–5% CPU—while giving you a searchable history.

Key catalog views:

• sys.query_store_query – query text and metadata
• sys.query_store_plan – execution plans, including is_forced_plan flag
• sys.query_store_runtime_stats – aggregated runtime stats per plan per interval
• sys.query_store_runtime_stats_interval – the time windows themselves

Worked Configuration Example

Run the following in the context of the target database (requires ALTER DATABASE permission, typically db_owner or sysadmin). Adjust values for your workload.

ALTER DATABASE WideWorldImporters SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 500,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO
);

Parameter notes:

• OPERATION_MODE = READ_WRITE – actively captures; READ_ONLY pauses capture but retains data.
• QUERY_CAPTURE_MODE = AUTO – filters out trivial/low-cost queries. Use ALL only on low-throughput systems; it can fill storage fast.
• MAX_STORAGE_SIZE_MB = 500 – cap for Query Store data. When hit, capture stops unless SIZE_BASED_CLEANUP_MODE = AUTO (default ON) evicts oldest data.
• STALE_QUERY_THRESHOLD_DAYS = 14 – queries not seen in 14 days are cleaned up. Default is 30; shorten for high-churn workloads.

Risk: Query Store lives in the primary filegroup. Ensure that filegroup has headroom and sits on fast storage. If the database is restored without the Query Store data (e.g., RESTORE DATABASE ... WITH REPLACE on a fresh instance), forced plans are lost.

Finding Regressions and Forcing a Plan

After workload runs, identify a regressed query. This example finds queries whose average duration doubled in the most recent interval compared to the prior one.

WITH recent AS (
    SELECT q.query_id, p.plan_id,
           rs.avg_duration,
           rs.interval_id
    FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    WHERE rs.interval_id = (SELECT MAX(interval_id) FROM sys.query_store_runtime_stats_interval)
),
previous AS (
    SELECT q.query_id, p.plan_id,
           rs.avg_duration,
           rs.interval_id
    FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    WHERE rs.interval_id = (SELECT MAX(interval_id) - 1 FROM sys.query_store_runtime_stats_interval)
)
SELECT r.query_id, r.plan_id AS recent_plan, p.plan_id AS previous_plan,
       r.avg_duration AS recent_avg_ms, p.avg_duration AS previous_avg_ms,
       (r.avg_duration * 1.0 / NULLIF(p.avg_duration,0)) AS ratio
FROM recent r
JOIN previous p ON p.query_id = r.query_id
WHERE r.avg_duration > 2 * p.avg_duration
ORDER BY ratio DESC;

Review the plans (use query_plan XML from sys.query_store_plan) to confirm the recent plan is genuinely worse. Then force the better plan:

EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 17;

Requires EXECUTE permission on sp_query_store_force_plan (granted to db_owner). Forced plans persist across restarts but not across restores that drop Query Store data.

Verification: Is It Actually Working?

Run this check after enabling and after a workload cycle:

SELECT actual_state_desc,
       readonly_reason,
       current_storage_size_mb,
       max_storage_size_mb,
       flush_interval_seconds,
       interval_length_minutes,
       query_capture_mode_desc,
       size_based_cleanup_mode_desc
FROM sys.database_query_store_options;

Expect actual_state_desc = 'READ_WRITE' and readonly_reason = 0. If readonly_reason is non-zero, Query Store switched to read-only (common causes: storage limit reached, database read-only, or corruption).

In SSMS, expand the database → Query Store node. Built-in reports like Regressed Queries and Tracked Queries visualize the same data.

To confirm capture cadence, execute a test query, then:

SELECT start_time, end_time, interval_id
FROM sys.query_store_runtime_stats_interval
ORDER BY interval_id DESC;

You should see intervals matching your INTERVAL_LENGTH_MINUTES setting.

Limits and Common Mistakes

Mistake	Impact	Fix
QUERY_CAPTURE_MODE = ALL on high-throughput OLTP	Storage fills fast; cleanup can't keep up; Query Store goes read-only	Use AUTO (default) or CUSTOM with execution count/duration thresholds
Ignoring sys.database_query_store_options monitoring	Silent switch to read-only; gaps in history	Add a monitoring alert on current_storage_size_mb / max_storage_size_mb > 0.8
Forcing a plan without validating data distribution	Plan optimal for old stats becomes disastrous after data skew	Review forced plans monthly: SELECT * FROM sys.query_store_plan WHERE is_forced_plan = 1;
Assuming forced plans survive restore	Disaster recovery loses forced plans if Query Store data isn't restored	Include Query Store in backup/restore test plans; script forced plans for re-application
Expecting capture for cross-database or system-db queries	Those queries never appear in Query Store	Run the query in the target database context or enable Query Store there

Hard Limits to Know

• Soft limit: ~100,000 distinct queries per database. Beyond this, new queries may not be tracked.
• Plan forcing does not work for natively compiled modules (memory-optimized tables) or queries with OPTION (RECOMPILE).
• In Azure SQL Database, Query Store is enabled by default for new databases. Configuration differs: no MAX_STORAGE_SIZE_MB (managed by platform), and some cleanup options are fixed.
• Query Store data resides in the primary filegroup. You cannot move it to a separate filegroup.

Rollback: Disabling Query Store

If you must disable (e.g., storage emergency), run:

ALTER DATABASE WideWorldImporters SET QUERY_STORE = OFF;

This stops capture and flushes pending data. The internal tables remain but become inaccessible. To purge all Query Store data, use ALTER DATABASE ... SET QUERY_STORE CLEAR ALL (requires exclusive database lock). Neither operation rolls back forced plans—they're simply no longer enforced because Query Store is off.

Quick Checklist Before You Enable

1. Confirm primary filegroup has ≥ 2× MAX_STORAGE_SIZE_MB free.
2. Set QUERY_CAPTURE_MODE = AUTO unless you have a specific reason for ALL.
3. Set SIZE_BASED_CLEANUP_MODE = AUTO (default).
4. Schedule a monthly review of sys.query_store_plan WHERE is_forced_plan = 1.
5. Add a monitoring query for actual_state_desc and storage utilization.

With these guards in place, Query Store becomes a reliable flight recorder for query performance—not a surprise storage consumer.