SQL Server Query Store Health

Why this problem matters

If Query Store unexpectedly becomes read-only, new plan and runtime data may not be collected. Size limits, internal state, or configuration issues can create gaps in performance history.

How to diagnose it

Check actual_state_desc, readonly_reason, current size, and capture mode from the same view. A difference between desired and actual state warrants further investigation.

The query displays Query Store configuration and runtime state for the current database without changing it. readonly_reason is a bitmask and should be interpreted using its documented meanings.

SELECT desired_state_desc,
       actual_state_desc,
       readonly_reason,
       current_storage_size_mb,
       max_storage_size_mb,
       query_capture_mode_desc,
       stale_query_threshold_days
FROM sys.database_query_store_options;

A safe solution approach

Review storage quota, retention policy, and capture mode in a controlled manner according to the cause. Before cleanup or configuration changes, determine whether required historical data will be preserved.

Why continuous monitoring matters

If a Query Store state change goes unnoticed, required history may be missing during the next performance incident. moon can monitor its health and surface degradation through Slack, PagerDuty, or webhooks.