How to Monitor SQL Server Query Store Settings and Health via T-SQL

Summary: Use the sys.database_query_store_options view to audit your Query Store configuration, monitor storage limits, and verify the current capture and cleanup modes.

Monitoring SQL Server Query Store Settings

The Query Store is often described as a "flight data recorder" for your database. To ensure it is functioning correctly and not hitting storage limits, you need to monitor the sys.database_query_store_options system view. This is essential for troubleshooting scenarios where the Query Store may have automatically switched to a Read-Only state.

Query Store Configuration Audit Script

The following query provides a quick breakdown of your current and desired Query Store states, storage consumption, and cleanup policies.

-- Audit Query Store health and configuration settings
SELECT
    actual_state_desc,               -- Current state (e.g., READ_WRITE or READ_ONLY)
    desired_state_desc,              -- The state you have requested
    interval_length_minutes,         -- Data aggregation interval
    current_storage_size_mb,         -- Current footprint on disk
    max_storage_size_mb,             -- Max allowed size before cleanup/read-only
    query_capture_mode_desc,         -- Capture policy (ALL, AUTO, or NONE)
    size_based_cleanup_mode_desc,    -- Automatic cleanup behavior
    readonly_reason                  -- Why the Query Store is currently read-only
FROM sys.database_query_store_options WITH (NOLOCK)
OPTION (RECOMPILE);
        

Key Metrics & Optimization Hints:

• actual_state_desc vs desired_state_desc: If the actual state is READ_ONLY while the desired is READ_WRITE, check the readonly_reason. It is likely that the max_storage_size_mb has been reached.
• WITH (NOLOCK): This hint performs a "dirty read." While it prevents locking contention on high-traffic systems, always use it carefully if precise consistency is required.
• OPTION (RECOMPILE): This forces a fresh execution plan. For system views like this, it ensures you aren't looking at stale cached metadata from a previous run.

---

For a complete deep dive into Query Store maintenance and best practices, refer to the official Microsoft Query Store Documentation.

Is your Query Store filling up? Consider changing your QUERY_CAPTURE_MODE to AUTO to capture only the most relevant queries and save significant disk space!