SQL Server Query Store FAQ – Enabling, Purging, and Disabling

ColumnStores, Data Analytics, Data Strategy, DBA, MinervaDB, SQL, SQL Performance, SQL Server

SQL Server Query Store FAQ

SQL Server Query Store FAQ: Enabling, Purging, and Disabling the Query Store in SQL Server

SQL Server’s Query Store is a powerful performance monitoring feature that captures query execution history, plans, and runtime statistics. Introduced in SQL Server 2016, it has become an essential tool for database administrators(DBA) and developers seeking to troubleshoot performance issues and maintain optimal database performance. This comprehensive guide covers everything you need to know about enabling, configuring, purging, and disabling Query Store in SQL Server.

What is Query Store?

Query Store provides insight into query plan choice and performance for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. It automatically captures a history of queries, plans, and runtime statistics, retaining these for review and separating data by time windows so you can see database usage patterns and understand when query plan changes happened on the server.

The Query Store simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Common scenarios include quickly finding and fixing plan performance regressions by forcing previous query plans, fixing queries that have recently regressed due to execution plan changes, and determining query execution frequency within specific time windows.

Enabling Query Store

Basic Enablement

Starting with SQL Server 2022, Query Store is enabled by default on new databases. However, for earlier versions or when you need to enable it manually, use the following T-SQL command:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

This command enables Query Store with default settings and begins capturing data immediately.

Advanced Configuration During Enablement

For production environments, you’ll typically want to configure specific parameters during enablement:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 200,
    WAIT_STATS_CAPTURE_MODE = ON
);

Query Store Configuration Parameters

Understanding each configuration parameter is crucial for optimal Query Store performance:

OPERATION_MODE

The OPERATION_MODE parameter controls whether Query Store actively collects data:

  • READ_WRITE: Query Store collects and persists query plan and runtime execution statistics information (default)
  • READ_ONLY: Information can be read from Query Store, but new information isn’t added. Automatically set when maximum storage space is exhausted

MAX_STORAGE_SIZE_MB

This parameter determines the space allocated to Query Store:

  • Default: 100 MB for SQL Server 2016-2017, 1000 MB for SQL Server 2019 and later
  • Behavior: The limit isn’t strictly enforced. When breached, Query Store transitions to read-only mode but can switch back to read-write once space is cleared

INTERVAL_LENGTH_MINUTES

Controls the time interval for aggregating runtime execution statistics:

  • Default: 60 minutes
  • Purpose: Optimizes space usage by aggregating runtime execution statistics over fixed time windows

DATA_FLUSH_INTERVAL_SECONDS

Determines how frequently data is persisted to disk:

  • Default: 900 seconds (15 minutes)
  • Purpose: Optimizes performance by asynchronously writing collected data to disk

CLEANUP_POLICY

Manages data retention through the STALE_QUERY_THRESHOLD_DAYS setting:

  • Default: 30 days
  • Function: Determines how long information for a query is kept in Query Store

SIZE_BASED_CLEANUP_MODE

Controls automatic cleanup when approaching storage limits:

  • AUTO: Automatically activates when size reaches 90% of MAX_STORAGE_SIZE_MB, removing least expensive and oldest queries first, stopping at approximately 80%
  • OFF: Disables automatic cleanup

QUERY_CAPTURE_MODE

Defines the active query capture policy:

  • ALL: Captures all queries (default for SQL Server 2016-2017)
  • AUTO: Captures relevant queries based on execution count and resource consumption (default for SQL Server 2019+ and Azure SQL Database)
  • NONE: Stops capturing new queries while continuing to collect statistics for already captured queries
  • CUSTOM: Available in SQL Server 2019+, allows control over query capture policies

MAX_PLANS_PER_QUERY

Sets the maximum number of plans maintained for each query:

  • Default: 200 plans per query

WAIT_STATS_CAPTURE_MODE

Controls wait statistics capture (SQL Server 2017+):

  • ON: Captures wait statistics information per query (default)
  • OFF: Disables wait statistics capture

Purging Query Store Data

Complete Data Purge

To remove all Query Store data while preserving configuration settings, use the CLEAR command:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;

For a more comprehensive cleanup that removes all query-related data and metadata:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR ALL;

Important Considerations for Purging

The CLEAR command can sometimes be blocked by background tasks, particularly the “QUERY STORE BACKGROUND FLUSH DB” process. In production environments, monitor the operation to ensure it completes successfully.

After clearing, Query Store configuration settings are preserved, and the feature remains enabled, immediately beginning to capture new data.

Using SQL Server Management Studio

You can also purge data through SSMS:

  1. Open SSMS and expand the Databases node
  2. Right-click on the database with Query Store enabled
  3. Select Properties
  4. Choose “Query Store” in the left panel
  5. Click the “Purge Query Data” button

Disabling Query Store

Standard Disable Command

To disable Query Store completely:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF;

Forced Disable

For faster shutdown, particularly useful in maintenance scenarios:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF (FORCED);

Azure SQL Database Limitation

Important: Query Store cannot be disabled in Azure SQL Database single database and Elastic Pool configurations. Attempting to execute ALTER DATABASE [database] SET QUERY_STORE = OFF will return a warning that this operation is not supported.

Performance Impact and Overhead

Minimal Performance Impact

Research by SQL Server experts indicates that the majority of workloads won’t see a significant impact on system performance when Query Store is enabled. Microsoft has prioritized keeping Query Store overhead low as a core feature priority, maintaining overall performance even if it means temporarily stopping query capture.

Overhead Management

Query Store is designed to minimize impact on user workloads. If the system detects potential performance issues, Query Store may temporarily stop capturing queries and runtime statistics to preserve system performance.

Version-Specific Features and Differences

SQL Server 2016

  • Initial Query Store release
  • Default storage: 100 MB
  • Default capture mode: ALL

SQL Server 2017

  • Added WAIT_STATS_CAPTURE_MODE parameter
  • Enhanced performance and reliability

SQL Server 2019

  • Default storage increased to 1000 MB
  • Default capture mode changed to AUTO
  • Introduced CUSTOM capture policies

SQL Server 2022

  • Query Store enabled by default for new databases
  • Added Query Store hints feature
  • Enhanced integration with Azure services

Monitoring Query Store Status

To check current Query Store configuration, query the system catalog view:

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,
    size_based_cleanup_mode_desc,
    query_capture_mode_desc,
    max_plans_per_query,
    wait_stats_capture_mode_desc
FROM sys.database_query_store_options;

Best Practices for Query Store Management

Configuration Recommendations

  1. Start with defaults: The default parameters are suitable for most environments
  2. Monitor over time: Adjust configuration based on observed behavior and workload patterns
  3. Size appropriately: Ensure MAX_STORAGE_SIZE_MB accommodates your query volume and retention needs
  4. Enable automatic cleanup: Use SIZE_BASED_CLEANUP_MODE = AUTO to prevent storage issues

Maintenance Strategies

  1. Regular monitoring: Check Query Store status and storage utilization regularly
  2. Periodic cleanup: Consider manual purging during maintenance windows for high-volume environments
  3. Retention tuning: Adjust STALE_QUERY_THRESHOLD_DAYS based on your troubleshooting needs
  4. Capture mode optimization: Use AUTO mode for most workloads to balance coverage and overhead

Troubleshooting Common Issues

  1. Read-only mode: If Query Store enters read-only mode, check storage utilization and consider purging old data or increasing MAX_STORAGE_SIZE_MB
  2. Performance concerns: Monitor system performance after enabling and adjust capture modes if necessary
  3. Blocked purge operations: If CLEAR commands hang, check for active background tasks and consider scheduling during low-activity periods

Recovery and Consistency

If Query Store experiences corruption or consistency issues, SQL Server 2017 and later versions provide a recovery mechanism:

-- Disable Query Store first
ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF;

-- Execute consistency check and recovery
EXEC sys.sp_query_store_consistency_check;

-- Re-enable Query Store
ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

Conclusion

Query Store is an invaluable tool for SQL Server performance management, offering detailed insights into query behavior and execution patterns. Proper configuration, monitoring, and maintenance ensure that Query Store provides maximum benefit with minimal overhead. Whether you’re enabling Query Store for the first time, optimizing its configuration, or managing data retention through purging operations, following the best practices outlined in this guide will help you leverage this powerful feature effectively.

Remember that Query Store configuration should be tailored to your specific workload characteristics and troubleshooting requirements. Start with default settings, monitor performance and storage utilization, and adjust parameters as needed to achieve optimal results for your environment.

Further Reading:

About MinervaDB Corporation 221 Articles
Full-stack Database Infrastructure Architecture, Engineering and Operations Consultative Support(24*7) Provider for PostgreSQL, MySQL, MariaDB, MongoDB, ClickHouse, Trino, SQL Server, Cassandra, CockroachDB, Yugabyte, Couchbase, Redis, Valkey, NoSQL, NewSQL, SAP HANA, Databricks, Amazon Resdhift, Amazon Aurora, CloudSQL, Snowflake and AzureSQL with core expertize in Performance, Scalability, High Availability, Database Reliability Engineering, Database Upgrades/Migration, and Data Security.
Website Facebook Twitter YouTube LinkedIn

Related Articles