Query Store: A Hidden Gem for Performance Troubleshooting in SQL Server

Introduction

In the world of SQL Server performance tuning, Query Store is like having a black box recorder for your database queries. Introduced in SQL Server 2016, this powerful feature tracks query performance over time, making it invaluable for troubleshooting regression issues and maintaining consistent performance. This comprehensive guide will show you how to unlock Query Store's full potential.



What is Query Store?

Query Store is a SQL Server feature that:

  • Automatically captures query history and execution plans

  • Tracks performance metrics over time

  • Allows you to force optimal execution plans

  • Provides historical data for performance analysis

  • Works at the database level (available SQL Server 2016+)

Enabling and Configuring Query Store

Basic Setup

-- Enable Query Store for a database
ALTER DATABASE YourDatabase 
SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE, -- Or READ_ONLY for maintenance
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900, -- 15 minutes
    MAX_STORAGE_SIZE_MB = 1024, -- Adjust based on needs
    INTERVAL_LENGTH_MINUTES = 60, -- Statistics aggregation interval
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO -- Can be ALL, AUTO, or NONE
);

Recommended Production Configuration

ALTER DATABASE YourDatabase 
SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14),
    DATA_FLUSH_INTERVAL_SECONDS = 300, -- 5 minutes
    MAX_STORAGE_SIZE_MB = 2048, -- 2GB
    INTERVAL_LENGTH_MINUTES = 15, -- More granular intervals
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 200
);

Key Query Store Use Cases

1. Identifying Performance Regressions

SELECT 
    q.query_id,
    qt.query_sql_text,
    rs.avg_duration/1000 AS avg_duration_ms,
    rs.avg_duration/1000 - LAG(rs.avg_duration/1000) OVER (
        PARTITION BY q.query_id ORDER BY rs.start_time) AS duration_change_ms,
    rs.start_time,
    rs.end_time,
    p.plan_id,
    TRY_CONVERT(XML, p.query_plan) AS query_plan
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
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.start_time >= DATEADD(day, -1, GETUTCDATE())
ORDER BY duration_change_ms DESC;

2. Forcing Optimal Execution Plans

-- Find the better performing plan
SELECT 
    q.query_id,
    qt.query_sql_text,
    p.plan_id,
    p.is_forced_plan,
    rs.avg_duration/1000 AS avg_duration_ms,
    rs.count_executions,
    TRY_CONVERT(XML, p.query_plan) AS query_plan
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
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE qt.query_sql_text LIKE '%YourProblemQuery%'
ORDER BY rs.avg_duration;

-- Force the better plan
EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;

3. A/B Testing Query Changes

-- Before making changes
DECLARE @before_avg FLOAT, @before_count INT;

SELECT 
    @before_avg = AVG(rs.avg_duration),
    @before_count = SUM(rs.count_executions)
FROM sys.query_store_query q
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
WHERE q.query_id = 123;

-- After implementing changes
SELECT 
    (AVG(rs.avg_duration) - @before_avg) AS duration_diff,
    (SUM(rs.count_executions) - @before_count) AS execution_diff,
    ((@before_avg * @before_count) - (AVG(rs.avg_duration) * SUM(rs.count_executions))) / 
        NULLIF(@before_avg * @before_count, 0) * 100 AS percent_improvement
FROM sys.query_store_query q
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
WHERE q.query_id = 123
AND rs.start_time > @change_date;

Advanced Query Store Techniques

1. Custom Capture Policies

-- Change capture mode to focus on important queries
ALTER DATABASE YourDatabase 
SET QUERY_STORE (QUERY_CAPTURE_MODE = CUSTOM);

-- Configure custom capture policies
ALTER DATABASE YourDatabase 
SET QUERY_STORE (
    QUERY_CAPTURE_MODE = CUSTOM,
    QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100,
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS
    )
);

2. Correlation with Wait Statistics

SELECT 
    q.query_id,
    qt.query_sql_text,
    ws.wait_category_desc,
    SUM(ws.total_query_wait_time_ms) AS total_wait_time_ms,
    SUM(ws.avg_query_wait_time_ms * ws.count_executions) AS weighted_avg_wait_time_ms,
    SUM(ws.count_executions) AS total_executions
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_id = qt.query_id
JOIN sys.query_store_wait_stats ws ON q.query_id = ws.query_id
WHERE ws.start_time >= DATEADD(day, -1, GETUTCDATE())
GROUP BY q.query_id, qt.query_sql_text, ws.wait_category_desc
ORDER BY total_wait_time_ms DESC;

3. Automatic Tuning with Query Store

-- Enable automatic plan correction (SQL Server 2017+)
ALTER DATABASE YourDatabase 
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

-- Monitor automatic tuning actions
SELECT * FROM sys.dm_db_tuning_recommendations;

Query Store Maintenance

1. Monitoring Query Store Health

SELECT 
    desired_state_desc,
    actual_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    flush_interval_seconds,
    interval_length_minutes,
    stale_query_threshold_days
FROM sys.database_query_store_options;

2. Cleaning Up Query Store

-- Clean up ad-hoc queries
EXEC sp_query_store_remove_query @query_id = 123;

-- Clean up all queries not executed in last 7 days
EXEC sp_query_store_remove_queries @before_date = DATEADD(day, -7, GETUTCDATE());

-- Reset Query Store (last resort)
ALTER DATABASE YourDatabase SET QUERY_STORE CLEAR;

Real-World Troubleshooting Scenarios

1. The Case of the Disappearing Index

-- Find queries that stopped using an index
WITH IndexUsage AS (
    SELECT 
        q.query_id,
        qt.query_sql_text,
        p.plan_id,
        TRY_CONVERT(XML, p.query_plan) AS query_plan,
        rs.avg_logical_io_reads,
        rs.start_time,
        LAG(TRY_CONVERT(XML, p.query_plan)) OVER (
            PARTITION BY q.query_id ORDER BY rs.start_time) AS prev_plan
    FROM sys.query_store_query q
    JOIN sys.query_store_query_text qt ON q.query_id = qt.query_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
    WHERE qt.query_sql_text LIKE '%YourTable%'
)
SELECT *
FROM IndexUsage
WHERE query_plan.exist('//RelOp[@PhysicalOp="Index Scan" or @PhysicalOp="Index Seek"]') = 1
AND prev_plan.exist('//RelOp[@PhysicalOp="Index Scan" or @PhysicalOp="Index Seek"]') = 0
ORDER BY start_time DESC;

2. Parameter Sniffing Investigation

-- Analyze parameter sensitivity
SELECT 
    q.query_id,
    qt.query_sql_text,
    p.plan_id,
    rs.avg_duration/1000 AS avg_duration_ms,
    rs.avg_logical_io_reads,
    rs.count_executions,
    rs.avg_parameter_list_size,
    TRY_CONVERT(XML, p.query_plan) AS query_plan
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_id = qt.query_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
WHERE qt.query_sql_text LIKE '%YourProc%'
ORDER BY q.query_id, rs.avg_duration DESC;

Best Practices for Query Store

  1. Start with AUTO capture mode and adjust as needed

  2. Set retention to 7–30 days depending on workload

  3. Monitor storage usage and adjust MAX_STORAGE_SIZE_MB accordingly

  4. Use CUSTOM capture policies on high-load systems

  5. Combine with Extended Events for deeper analysis

  6. Document forced plans to avoid technical debt

  7. Review Query Store stats regularly (e.g., weekly)

Conclusion

Query Store revolutionizes SQL Server performance troubleshooting by:

  • Providing historical query performance data

  • Enabling easy plan regression detection

  • Offering simple plan forcing capabilities

  • Supporting quantitative performance analysis

By implementing Query Store following the patterns in this guide, you'll be able to:

✅ Proactively identify performance issues
✅ Quickly diagnose query regressions
✅ Validate performance improvements
✅ Maintain stable query performance over time

Query Store is not a "set it and forget it" feature. Regular monitoring and maintenance are key to getting the most value out of it. When properly configured and managed, it becomes one of the most powerful tools in your SQL Server performance tuning arsenal.

Comments

Post a Comment

Popular posts from this blog

Migrating SQL Server to Azure SQL Database: A Step-by-Step Guide

Migrating from on-premises SQL Server to Azure SQL Database offers scalability, cost-efficiency, and built-in high availability. However, the process requires careful planning to avoid downtime and compatibility issues. This guide covers: ✅ Pre-Migration Assessment ✅ Choosing the Right Azure SQL Option ✅ Step-by-Step Migration Methods ✅ Post-Migration Validation ✅ Common Pitfalls & Best Practices 1. Pre-Migration Assessment A. Check Compatibility Issues Azure SQL Database has some limitations compared to SQL Server. Verify compatibility using: 1. Microsoft Data Migration Assistant (DMA) Download & Install : Microsoft DMA Steps : Create a new assessment project. Select "Azure SQL Database" as the target. Analyze for: Unsupported features (e.g., SQL Agent Jobs , Cross-DB Queries ) Syntax differences (e.g., WITH (NOLOCK) → WITH (READUNCOMMITTED) ) 2. Azure SQL Migration Extension (VS Code) Useful for automated schema and T-SQL validat...
Read more

MS SQL Server Performance Optimization: Best Practices & Tips

Microsoft SQL Server is a powerful relational database management system, but as data grows and queries become more complex, performance can take a hit. Optimizing SQL Server performance is crucial for ensuring fast query execution, efficient resource utilization, and a seamless user experience. In this blog, we’ll explore essential MS SQL Server performance optimization techniques, covering indexing, query tuning, memory management, and more. 1. Optimize Indexing Strategy Indexes play a vital role in improving query performance by speeding up data retrieval. Here’s how to optimize them: Use the Right Index Types Clustered Index : Stores data in a sorted structure. Best for primary keys and frequently searched columns. Non-Clustered Index : Improves query performance on frequently filtered columns. Filtered Index : Ideal for queries that filter a subset of data. Avoid Over-Indexing Too many indexes slow down INSERT, UPDATE, DELETE operations. Regularly analyze index usag...
Read more

Common Causes of Slow Queries in SQL Server and How to Fix Them

Slow queries are one of the most common performance issues in SQL Server environments, often leading to frustrated users and degraded application performance. Identifying and resolving these bottlenecks is crucial for maintaining an efficient database system. In this article, we'll explore the most frequent causes of slow queries in SQL Server and provide actionable solutions to address them. 1. Missing or Inefficient Indexes Problem: One of the most common reasons for slow queries is the lack of proper indexes or the existence of inefficient indexes. Symptoms: High table scan operations (shown in execution plans) High logical reads in query statistics Slow performance on WHERE, JOIN, or ORDER BY clauses Solutions: -- Create appropriate indexes CREATE INDEX IX_Customer_LastName ON Customers(LastName) INCLUDE (FirstName, Email); -- For complex queries, consider filtered indexes CREATE INDEX IX_Orders_Recent ON Orders(OrderDate) WHERE OrderDate > DATEADD(year, -1...
Read more