SQL Server Query Store: Configuration, Risks, and Keeping It Healthy

If Query Store is not enabled, you do not have query plan history or query performance statistics. Without that, you cannot see how a query performed yesterday, what plan it used, when it changed, or why it is slower today.

This is a clean, production-safe Query Store enablement that captures what matters and avoids unnecessary overhead.

Enable Query Store

ALTER DATABASE YourDBName
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 = 1024,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

Configuration Breakdown

• OPERATION_MODE = READ_WRITE - Enables Query Store capture.
• QUERY_CAPTURE_MODE = AUTO - SQL Server filters out low-value, one-off queries. This is the single biggest control on overhead.
• CLEANUP_POLICY = 30 days - Removes stale plans automatically. Keeps the dataset relevant and manageable.
• DATA_FLUSH_INTERVAL_SECONDS = 900 - Flushes in-memory runtime stats to disk every 15 minutes.
• MAX_STORAGE_SIZE_MB = 1024 - Caps growth. Prevents uncontrolled expansion and protects the database from Query Store consuming excessive space.
• INTERVAL_LENGTH_MINUTES = 60 - Aggregates runtime stats into 60-minute buckets.
• SIZE_BASED_CLEANUP_MODE = AUTO - Allows SQL Server to clean up older Query Store data when storage pressure occurs.
• MAX_PLANS_PER_QUERY = 200 - Limits the maximum number of unique plans stored for a single query.
• WAIT_STATS_CAPTURE_MODE = ON - Captures query-level wait stats for troubleshooting.

Verify It Is Working

SELECT 
    query_capture_mode_desc,
    actual_state_desc,
    current_storage_size_mb,
    readonly_reason,
    max_storage_size_mb,
    max_plans_per_query,
    size_based_cleanup_mode_desc,
    wait_stats_capture_mode_desc
FROM sys.database_query_store_options;

The only problem is that the above must be run inside each database. To return details from all online user databases where Query Store is enabled, use this:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N'
SELECT ''' + name + N''' AS database_name,
    query_capture_mode_desc,
    actual_state_desc,
    current_storage_size_mb,
    readonly_reason,
    max_storage_size_mb,
    max_plans_per_query,
    size_based_cleanup_mode_desc,
    wait_stats_capture_mode_desc,
    CAST(100.0 * current_storage_size_mb / NULLIF(max_storage_size_mb,0) AS DECIMAL(5,2)) AS pct_used
FROM   ' + QUOTENAME(name) + N'.sys.database_query_store_options
UNION ALL'
FROM sys.databases
WHERE is_query_store_on = 1
  AND state_desc = 'ONLINE'
  AND database_id > 4;

SET @sql = LEFT(@sql, LEN(@sql) - LEN('UNION ALL'));

EXEC sp_executesql @sql;

What Query Store Gives You

• Historical query performance, not just what is happening right now
• Execution plan history
• Plan regression detection
• Ability to force a known good execution plan when regressions occur
• Visibility into top CPU, duration, IO, and wait consumers over time
• Hard evidence of performance changes after changes or data shifts occur
• Proactive performance management

What Happens Without It

• No historical query performance data
• No execution plan history
• No way to identify when a plan changed
• No (fast) ability to compare before vs after
• Longer incident resolution time
• Reactive troubleshooting only

Risks and Operational Reality

With the proper configuration, Query Store is largely self-managing, but it should not be treated as 'set it and forget it'.

• Storage pressure - Controlled by max size and automatic cleanup. If storage fills or cleanup cannot keep up, Query Store can become READ_ONLY and stop capturing new runtime data.
• Capture overhead - Controlled by AUTO capture mode. AUTO filters out noise. ALL mode can introduce more overhead on high-throughput systems.
• Internal state issues - Query Store can still enter ERROR, OFF, or READ_ONLY states depending on database state, storage, or internal limits.
• Configuration drift - Someone can change capture mode, storage limits, cleanup settings, or wait stats capture later.

Recommended Monitoring

A lightweight SQL Agent job can monitor Query Store state, storage usage, and basic health. The goal is to know when Query Store state has changed for a database, when storage usage is too high, or if it has stopped doing what you enabled it to do.

Example job step:

EXEC DBA.dbo.usp_QueryStoreHealthCheck;

The procedure below checks all online user databases where Query Store is enabled and sends an email alert when Query Store is not READ_WRITE or storage usage exceeds the configured threshold.

USE DBA;
GO

CREATE OR ALTER PROCEDURE dbo.usp_QueryStoreHealthCheck
AS
/*
    Used to monitor Query Store and send an alert when:
      - Query Store state is not READ_WRITE
      - Query Store storage usage exceeds the configured threshold

    Usage:
      EXEC dbo.usp_QueryStoreHealthCheck;
*/
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @threshold_pct    DECIMAL(5,2)  = 80.0,
        @mail_profile     SYSNAME       = 'YourMailProfile',
        @mail_recipients  NVARCHAR(500) = 'YourEmail@Wherever.com';

    IF OBJECT_ID('tempdb..#qs') IS NOT NULL DROP TABLE #qs;

    CREATE TABLE #qs
    (
        database_name                SYSNAME,
        actual_state_desc            NVARCHAR(60),
        current_storage_size_mb      BIGINT,
        max_storage_size_mb          BIGINT,
        pct_used                     DECIMAL(5,2),
        query_capture_mode_desc      NVARCHAR(60),
        wait_stats_capture_mode_desc NVARCHAR(60),
        size_based_cleanup_mode_desc NVARCHAR(60)
    );

    DECLARE @sql NVARCHAR(MAX) = N'';

    SELECT @sql = @sql + N'
INSERT INTO #qs
SELECT ''' + name + N''',
       actual_state_desc,
       current_storage_size_mb,
       max_storage_size_mb,
       CAST(100.0 * current_storage_size_mb / NULLIF(max_storage_size_mb,0) AS DECIMAL(5,2)),
       query_capture_mode_desc,
       wait_stats_capture_mode_desc,
       size_based_cleanup_mode_desc
FROM   ' + QUOTENAME(name) + N'.sys.database_query_store_options;
'
    FROM sys.databases
    WHERE is_query_store_on = 1
      AND state_desc = 'ONLINE'
      AND database_id > 4;

    IF @sql = N''
        RETURN;

    EXEC sp_executesql @sql;

    IF NOT EXISTS
    (
        SELECT 1
        FROM #qs
        WHERE actual_state_desc <> 'READ_WRITE'
           OR pct_used >= @threshold_pct
    )
    BEGIN
        RETURN;
    END;

    DECLARE @body NVARCHAR(MAX) =
          N'<style>body{font-family:Verdana;font-size:9pt;color:#000080;}'
        + N'table{border-collapse:collapse;font-size:9pt;}th,td{border:1px solid #999;padding:4px 8px;}'
        + N'th{background:#000080;color:#fff;}</style>'
        + N'<p>Query Store health check on <b>' + @@SERVERNAME + N'</b> at '
        + CONVERT(NVARCHAR(20), SYSDATETIME(), 120)
        + N' detected one or more conditions requiring attention.</p>'
        + N'<p>Full Query Store state across all databases:</p>'
        + N'<table><tr>'
        + N'<th>Database</th>'
        + N'<th>State</th>'
        + N'<th>Used MB</th>'
        + N'<th>Max MB</th>'
        + N'<th>% Used</th>'
        + N'<th>Capture</th>'
        + N'<th>Wait Stats</th>'
        + N'<th>Cleanup</th>'
        + N'</tr>';

    SELECT @body = @body
        + N'<tr>'
        + N'<td>' + ISNULL(database_name, N'') + N'</td>'
        + N'<td>' + ISNULL(actual_state_desc, N'') + N'</td>'
        + N'<td>' + CAST(ISNULL(current_storage_size_mb,0) AS NVARCHAR(20)) + N'</td>'
        + N'<td>' + CAST(ISNULL(max_storage_size_mb,0) AS NVARCHAR(20)) + N'</td>'
        + N'<td>' + CAST(ISNULL(pct_used,0) AS NVARCHAR(10)) + N'</td>'
        + N'<td>' + ISNULL(query_capture_mode_desc, N'') + N'</td>'
        + N'<td>' + ISNULL(wait_stats_capture_mode_desc, N'') + N'</td>'
        + N'<td>' + ISNULL(size_based_cleanup_mode_desc, N'') + N'</td>'
        + N'</tr>'
    FROM #qs
    ORDER BY database_name;

    SET @body = @body + N'</table>';

    DECLARE @subject NVARCHAR(255) =
        N'Query Store alert on ' + @@SERVERNAME;

    EXEC msdb.dbo.sp_send_dbmail
         @profile_name = @mail_profile,
         @recipients   = @mail_recipients,
         @subject      = @subject,
         @body         = @body,
         @body_format  = 'HTML';
END;
GO

Bottom Line

When configured right, Query Store earns its keep — low overhead, high payoff, and the first place I look when something goes sideways, along with sp_BlitzCache.

More to Read:

Microsoft Docs: Monitoring Performance by Using Query Store
Microsoft Docs: Query Store Best Practices
Microsoft Docs: sys.database_query_store_options
Erik Darling: Query Store Improvements
Brent Ozar: sp_BlitzCache: Find Your Worst-Performing Queries