SQL Server Query Store: Complete Tutorial

Discover expert insights on SQL Server Query Store for SQL Server database administrators and developers.

Overview

SQL Server Query Store is a critical feature for performance monitoring and troubleshooting that every DBA should master. This comprehensive tutorial covers everything you need to know to leverage Query Store effectively.

Why This Matters

In today's data-driven world, understanding query performance over time is essential for maintaining optimal database performance. Query Store provides a "flight recorder" for your SQL Server queries.

Key Benefit: Query Store captures query execution plans and runtime statistics, making it easy to identify performance regressions and optimize problematic queries.

💡 Key Concepts

1. Query Performance History

• Capture execution plans and runtime statistics
• Track performance metrics over time
• Identify performance regressions automatically

2. Plan Forcing

• Force specific execution plans
• Prevent plan regressions
• Ensure consistent query performance

3. Performance Insights

• Analyze query patterns
• Identify resource-intensive queries
• Monitor parameter sniffing issues

Implementation Steps

Step 1: Enable Query Store

Enable Query Store on your database.

-- Enable Query Store
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO
);

Step 2: Verify Configuration

Check Query Store settings and status.

-- Check Query Store configuration
SELECT
    desired_state_desc,
    actual_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    query_capture_mode_desc,
    size_based_cleanup_mode_desc
FROM sys.database_query_store_options;

Step 3: Query Performance Analysis

Identify top resource-consuming queries.

-- Find top 10 queries by CPU time
SELECT TOP 10
    qsq.query_id,
    qsqt.query_sql_text,
    qsrs.count_executions,
    qsrs.avg_duration / 1000.0 AS avg_duration_ms,
    qsrs.avg_cpu_time / 1000.0 AS avg_cpu_time_ms,
    qsrs.avg_logical_io_reads,
    qsrs.last_execution_time
FROM sys.query_store_query qsq
INNER JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
INNER JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
INNER JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
WHERE qsrs.last_execution_time > DATEADD(DAY, -7, GETDATE())
ORDER BY qsrs.avg_cpu_time DESC;

Step 4: Plan Forcing

Force a specific execution plan for optimal performance.

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

-- Verify forced plans
SELECT
    qsq.query_id,
    qsp.plan_id,
    qsqt.query_sql_text,
    qsp.is_forced_plan,
    qsp.force_failure_count
FROM sys.query_store_query qsq
INNER JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
INNER JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
WHERE qsp.is_forced_plan = 1;

✅ Best Practices

• Regular monitoring - Review Query Store reports weekly
• Appropriate retention - Balance storage size with data retention needs
• Plan forcing - Use sparingly for critical queries only
• Performance baselines - Establish baselines before making changes
• Cleanup policies - Configure automatic cleanup to prevent storage issues

-- Identify queries with plan changes
SELECT
    qsq.query_id,
    qsqt.query_sql_text,
    COUNT(DISTINCT qsp.plan_id) AS plan_count,
    MAX(qsrs.last_execution_time) AS last_execution
FROM sys.query_store_query qsq
INNER JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
INNER JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
INNER JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
GROUP BY qsq.query_id, qsqt.query_sql_text
HAVING COUNT(DISTINCT qsp.plan_id) > 1
ORDER BY plan_count DESC;

⚠️ Common Challenges

1. Storage Management

• Monitor storage consumption
• Configure appropriate max size
• Enable size-based cleanup

-- Check Query Store space usage
SELECT
    current_storage_size_mb,
    max_storage_size_mb,
    CAST(current_storage_size_mb * 100.0 / max_storage_size_mb AS DECIMAL(5,2)) AS percent_used
FROM sys.database_query_store_options;

2. Performance Regressions

• Identify queries with degraded performance
• Compare execution plans
• Force better plans when needed

-- Find regressed queries
SELECT
    qsq.query_id,
    qsqt.query_sql_text,
    qsrs1.avg_duration AS recent_avg_duration,
    qsrs2.avg_duration AS historical_avg_duration,
    CAST((qsrs1.avg_duration - qsrs2.avg_duration) * 100.0 / qsrs2.avg_duration AS DECIMAL(5,2)) AS percent_regression
FROM sys.query_store_query qsq
INNER JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
INNER JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
INNER JOIN sys.query_store_runtime_stats qsrs1 ON qsp.plan_id = qsrs1.plan_id
INNER JOIN sys.query_store_runtime_stats qsrs2 ON qsp.plan_id = qsrs2.plan_id
WHERE qsrs1.last_execution_time > DATEADD(DAY, -1, GETDATE())
  AND qsrs2.last_execution_time BETWEEN DATEADD(DAY, -8, GETDATE()) AND DATEADD(DAY, -7, GETDATE())
  AND qsrs1.avg_duration > qsrs2.avg_duration * 1.2
ORDER BY percent_regression DESC;

3. Read-Only Mode

• Query Store enters read-only when max size reached
• Increase max storage or enable cleanup
• Clear old data if necessary

-- Clear Query Store data
ALTER DATABASE YourDatabase SET QUERY_STORE CLEAR ALL;

🚀 Tools and Resources

• SQL Server Management Studio - Built-in Query Store reports
• Azure Data Studio - Query Store dashboards
• Dynamic Management Views - Programmatic access to Query Store data
• SQL Server official documentation - Microsoft Learn Query Store guide

Real-World Examples

Organizations worldwide have successfully used Query Store to achieve:

• 70% faster troubleshooting of performance issues
• Automatic detection of plan regressions
• Significant reduction in performance-related incidents
• Improved application performance through plan forcing

Conclusion

Mastering SQL Server Query Store is essential for modern database administrators. Use this powerful feature to monitor, troubleshoot, and optimize query performance effectively.

Next Steps

1. Enable Query Store on your production databases
2. Configure appropriate retention and cleanup policies
3. Review Query Store reports regularly
4. Implement plan forcing for critical queries

For more SQL Server tutorials and expert guides, explore our comprehensive database resources.

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in SQL Server, SQL optimization, and enterprise database solutions.

Related Articles

• Database Performance Tuning Best Practices
• Cloud Database Migration Strategies
• High Availability Database Setup

Need help with SQL Server optimization? Contact us for expert database consulting.