Query Store Best Practices for Production Environments

Query Store is a powerful SQL Server feature that captures query execution history, making it easier to identify performance regressions and optimize workloads. Let's explore how to configure and use it effectively in production.

What is Query Store?

Introduced in SQL Server 2016, Query Store acts as a "flight data recorder" for your queries. It automatically captures:

• Query text and execution plans
• Runtime statistics (CPU, duration, I/O, memory)
• Plan changes over time
• Wait statistics per query (SQL Server 2017+)

Enabling Query Store

-- Enable Query Store with recommended settings
ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;
ALTER DATABASE [YourDatabase] SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 200,
    WAIT_STATS_CAPTURE_MODE = ON
);

Recommended Configuration

Storage Size

Database Size	Recommended Query Store Size
< 100 GB	1 GB
100 GB - 1 TB	2-4 GB
> 1 TB	4-10 GB

Capture Mode Options

• ALL: Captures every query (high overhead)
• AUTO: Captures significant queries based on execution count and resource consumption
• NONE: Stops capturing new queries
• CUSTOM: Fine-grained control (SQL Server 2019+)

For most production environments, AUTO provides the best balance.

Identifying Regressed Queries

One of Query Store's killer features is finding queries that got slower:

-- Find queries with plan regressions
SELECT 
    q.query_id,
    qt.query_sql_text,
    rs1.avg_duration AS old_duration,
    rs2.avg_duration AS new_duration,
    (rs2.avg_duration - rs1.avg_duration) / rs1.avg_duration * 100 AS pct_increase
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 p1 ON q.query_id = p1.query_id
JOIN sys.query_store_plan p2 ON q.query_id = p2.query_id
JOIN sys.query_store_runtime_stats rs1 ON p1.plan_id = rs1.plan_id
JOIN sys.query_store_runtime_stats rs2 ON p2.plan_id = rs2.plan_id
JOIN sys.query_store_runtime_stats_interval i1 ON rs1.runtime_stats_interval_id = i1.runtime_stats_interval_id
JOIN sys.query_store_runtime_stats_interval i2 ON rs2.runtime_stats_interval_id = i2.runtime_stats_interval_id
WHERE i1.end_time < i2.start_time
    AND rs2.avg_duration > rs1.avg_duration * 1.5
    AND rs1.count_executions > 100
ORDER BY pct_increase DESC;

Forcing Query Plans

When you identify a good plan, you can force SQL Server to use it:

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

-- Unforce when needed
EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 123;

Monitoring Query Store Health

-- Check Query Store status and space usage
SELECT 
    actual_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    CAST(current_storage_size_mb AS DECIMAL(10,2)) / 
        NULLIF(max_storage_size_mb, 0) * 100 AS pct_used,
    flush_interval_seconds,
    interval_length_minutes,
    stale_query_threshold_days
FROM sys.database_query_store_options;

Common Issues and Solutions

Query Store Goes Read-Only

This happens when storage is full or after database restore.

-- Reset to read-write mode
ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

-- If storage is full, increase size or purge old data
ALTER DATABASE [YourDatabase] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2048);

-- Or manually clean up
EXEC sp_query_store_flush_db;
EXEC sp_query_store_reset_exec_stats;

Too Many Plans Per Query

Parameter sniffing can cause plan explosion:

-- Find queries with many plans
SELECT 
    q.query_id,
    COUNT(p.plan_id) 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(p.plan_id) > 10
ORDER BY plan_count DESC;

Integration with PSG Stack

PSG Stack leverages Query Store data to provide:

• Automatic regression detection with alerts
• Historical trend analysis across time periods
• Plan comparison tools for troubleshooting
• Recommendations for plan forcing and optimization

Conclusion

Query Store is an essential tool for any production SQL Server environment. Proper configuration and regular monitoring will help you catch performance issues before they impact users.

In our next article, we'll explore advanced Query Store features in SQL Server 2019 and 2022.