How to Enable Query Store in SQL Server

by

Query Store is SQL Server’s built-in query performance tracking system that captures execution history, plans, and runtime statistics. From SQL Server 2022 it’s enabled by default for all newly created databases. But in earlier versions, it’s disabled by default, which means you’ll need to explicitly enable it on each database where you want to track query performance.

To enable Query Store, use the ALTER DATABASE command:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE = ON;

Replace YourDatabaseName with the name of your database. This enables Query Store with default settings, which are suitable for most databases.

Enabling with Custom Configuration

If you want to specify custom settings when enabling Query Store, you can include configuration options.

For example:

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

Configuration Options

  • OPERATION_MODE – Controls whether Query Store is actively capturing data (READ_WRITE) or just reading existing data (READ_ONLY)
  • QUERY_CAPTURE_MODE – Determines which queries get captured. ALL captures everything, AUTO captures queries that consume significant resources, CUSTOM uses advanced capture policies (see below), NONE disables capture
  • CLEANUP_POLICY – Sets how long to retain query data before automatically purging it (in days)
  • DATA_FLUSH_INTERVAL_SECONDS – Controls how often buffered data is written to disk
  • MAX_STORAGE_SIZE_MB – Sets the maximum disk space Query Store can use
  • INTERVAL_LENGTH_MINUTES – Defines how often runtime statistics are aggregated
  • SIZE_BASED_CLEANUP_MODE – When set to AUTO, automatically cleans up data when approaching max storage size; OFF disables automatic cleanup
  • MAX_PLANS_PER_QUERY – Limits the number of plans stored per query to prevent excessive storage usage
  • WAIT_STATS_CAPTURE_MODE – Enables or disables collection of wait statistics (SQL Server 2017+)

Using Custom Query Capture Mode

For advanced scenarios where you need fine-grained control over which queries are captured, you can use QUERY_CAPTURE_MODE = CUSTOM along with the following custom capture policies:

  • STALE_CAPTURE_POLICY_THRESHOLD – How long before a query’s cached plan is considered stale and recaptured (in DAYS or HOURS)
  • EXECUTION_COUNT – Minimum number of executions required to capture a query
  • TOTAL_COMPILE_CPU_TIME_MS – Minimum compile CPU time in milliseconds required to capture a query
  • TOTAL_EXECUTION_CPU_TIME_MS – Minimum execution CPU time in milliseconds required to capture a query

Here’s an example:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = CUSTOM,
    QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 10,
        TOTAL_COMPILE_CPU_TIME_MS = 100,
        TOTAL_EXECUTION_CPU_TIME_MS = 500
    ),
    MAX_STORAGE_SIZE_MB = 1000
);

This configuration only captures queries that meet all of the following criteria:

  • STALE_CAPTURE_POLICY_THRESHOLD – The query’s cached plan hasn’t been recaptured in the last 24 hours
  • EXECUTION_COUNT – The query has executed at least 10 times
  • TOTAL_COMPILE_CPU_TIME_MS – The query has consumed at least 100ms of compile CPU time
  • TOTAL_EXECUTION_CPU_TIME_MS – The query has consumed at least 500ms of execution CPU time

Custom capture mode can be useful for high-volume databases where capturing every query would consume too much storage, but AUTO mode is too restrictive. You can tune these thresholds to capture queries that matter to your specific workload while filtering out trivial queries.

Verifying Query Store is Enabled

To confirm Query Store is enabled and check its current state:

SELECT 
    actual_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb
FROM sys.database_query_store_options;

If actual_state_desc shows READ_WRITE, Query Store is enabled and capturing data.

Example output:

actual_state_desc  readonly_reason  current_storage_size_mb  max_storage_size_mb
-----------------  ---------------  -----------------------  -------------------
READ_WRITE         0                1                        100

in this case we can see that it’s enabled and capturing data.