Understanding SQL Server Query Store: A Comprehensive Guide

SQL Server Query Store is an essential feature introduced in SQL Server 2016, designed to provide insights into query performance by storing a history of executed queries, query plans, and runtime statistics. Acting as a flight recorder for your database, Query Store simplifies performance troubleshooting and helps DBAs optimize database workloads efficiently.

In this article, we will explore what Query Store is, its benefits, scenarios for its use, and steps to implement it with examples.

What Is SQL Server Query Store?

Query Store is a database-scoped feature that captures a history of queries, query execution plans, and performance statistics. This feature enables:

• Retention of performance data across server restarts. 
• Visibility into query plan changes over time.
• Identification of performance regressions caused by plan changes.

With Query Store, you can analyze query performance trends and make data-driven decisions to address issues such as query plan regressions or inefficiencies.

Benefits of Using Query Store

• Enhanced Performance Insights: Query Store provides detailed insights into query performance, execution plans, and runtime metrics over time, helping DBAs diagnose issues quickly.
• Plan Stability: You can force Query Store to use a specific execution plan to maintain consistent performance for critical queries.
• Simplified Troubleshooting: Query Store retains a history of queries and plans, making it easier to pinpoint changes that lead to performance degradation.
• Data Retention: Performance data is retained even after SQL Server restarts, ensuring continuity in analysis.
• Historical Comparisons: DBAs can compare historical query performance data to detect trends and anomalies.

When to Use Query Store

You should consider using Query Store in the following scenarios:

1. Performance Troubleshooting: When troubleshooting sudden performance degradations caused by query plan changes or inefficiencies.
2. Workload Optimization: For identifying queries with the highest resource consumption and optimizing them.
3. Testing Database Upgrades: To compare query performance before and after upgrades or migrations.
4. Application Development: To monitor how new or modified queries perform in a test environment before deployment.
5. Maintaining Query Plan Stability: To ensure consistent performance by forcing known efficient execution plans.

How to Implement Query Store

Enabling Query Store

Query Store is disabled by default. To enable it, use the following SQL command:

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON;

You can configure additional settings while enabling Query Store:

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,  -- Enables read-write mode
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),  -- Retain data for 30 days
    DATA_FLUSH_INTERVAL_SECONDS = 900,  -- Persist data to disk every 15 minutes
    MAX_STORAGE_SIZE_MB = 100  -- Limit storage to 100 MB
);

Query Store can also be enabled from SSMS:

• Right-click on the database you want to enable Query Store for.

• From the Operation Mode (Requested) drop-down box, choose the Read Write item:

Query Store Modes

• Read-Only: Query Store collects new data but prevents changes to existing data.
• Read-Write: Query Store actively collects and stores new data.
• Off: Disables Query Store but retains existing data.
• In the Operation Mode (Requested), in the drop-down box, the same options can be set as those mentioned for the previous option. Setting these values will have a direct impact on the state of the Query Store.

The equivalent T-SQL code for the same option is:

ALTER DATABASE AdventureWorks2014
SET QUERY_STORE = ON   
    (  
      OPERATION_MODE = READ_ONLY   
    );

In the Data Flush Interval (Minutes) option, an interval in minutes can be set, which shows how frequently the query runtime statistics and query execution plans will be flushed from the memory of the SQL Server instance to disk.

If this option is set to a lower value than the frequency of flushes, data from memory to disk will occur often, which will negatively impact the performance of the SQL Server instance. But if the value is increased, more SQL Server Query Store information will be placed in memory of the SQL Server instance before it flushes to disk, which increases the risk of losing that data in case of SQL Server restarts or crashes.

Below is the T-SQL code for setting the Data Flush Interval (Minutes) option

ALTER DATABASE AdventureWorks2014
SET QUERY_STORE = ON   
    (  
     DATA_FLUSH_INTERVAL_SECONDS = 900   
    );

The Statistics Collection Interval option defines the aggregation interval for query runtime statistics that should be used within the SQL Server Query Store. By default, it is set to 60 minutes. A lower value indicates finer granularity of query runtime statistics; thus, more intervals occur, which requires additional disk space for storing these statistics.

The T-SQL code for setting the Statistics Collection Interval option:

ALTER DATABASE AdventureWorks2014
SET QUERY_STORE = ON   
    (  
    INTERVAL_LENGTH_MINUTES = 1440   -- in Minutes
    );

In case that is in INTERVAL_LENGTH_MINUTES, set some other number:

ALTER DATABASE AdventureWorks2014
SET QUERY_STORE = ON   
    (  
    INTERVAL_LENGTH_MINUTES = 900   
    );

Msg 12432, Level 16, State 1, Line 1
SQL Server Query Store Interval length cannot be changed because an invalid value was provided. Please try again with a valid value (1, 5, 10, 15, 30 & 60).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

The Max Size (MB) option is for configuring the maximum size of the SQL Server Query Store. By default, the maximum size of the SQL Server Query Store is set to 1000 MB. The data in the SQL Server Query Store is stored in the database where the SQL Server Query Store is enabled. The SQL Server Query Store doesn’t auto-grow and once the SQL Server Query Store reaches the maximum size, the Operation Mode will be switched to the Read Only mode, automatically, and new query execution plan and query runtime statistics will not be collected.

Querying the Query Store

SQL Server provides system views to query the Query Store data:

• sys.query_store_query: Contains query text.
• sys.query_store_plan: Includes execution plans.
• sys.query_store_runtime_stats: Stores runtime metrics.
• sys.query_store_runtime_stats_interval: Defines the intervals for runtime statistics.

Example:

SELECT
    qsq.query_id,
    qsqp.plan_id,
    qsqp.query_plan,
    qsrs.avg_duration,
    qsrs.avg_cpu_time
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsqp
    ON qsq.query_id = qsqp.query_id
JOIN sys.query_store_runtime_stats AS qsrs
    ON qsqp.plan_id = qsrs.plan_id
WHERE qsrs.avg_duration > 1000;  -- Queries taking over 1000 ms

Forcing Query Plans

To force a specific query plan:

EXEC sp_query_store_force_plan
    @query_id = 1,  -- Replace with actual query ID
    @plan_id = 2;   -- Replace with actual plan ID

To un-force a plan:

EXEC sp_query_store_unforce_plan
    @query_id = 1;

Monitoring and Maintenance

Use the following query to monitor Query Store usage:

SELECT
    current_storage_size_mb,
    max_storage_size_mb,
    cleanup_policy_stale_query_threshold_days
FROM sys.database_query_store_options;

To clear Query Store data:

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE CLEAR;

Best Practices

• Set Appropriate Storage Limits: Configure MAX_STORAGE_SIZE_MB to prevent Query Store from consuming excessive disk space.
• Regular Maintenance: Review and clear old data using STALE_QUERY_THRESHOLD_DAYS.
• Monitor Impact: Evaluate Query Store’s overhead on your database’s performance and adjust settings accordingly.
• Test Before Production: Use Query Store in a test environment to fine-tune its configuration.

Conclusion

SQL Server Query Store is a powerful tool for DBAs to gain actionable insights into query performance and ensure consistent application performance. By understanding its capabilities, implementing it effectively, and adhering to best practices, you can leverage Query Store to optimize workloads, troubleshoot issues, and maintain stable database operations.