Monitoring Query Store Storage Usage in SQL Server (T-SQL)

by

When you have Query Store enabled in SQL Server, it consumes disk space in order to store query text, execution plans, and runtime statistics. Monitoring storage usage can help you avoid situations where Query Store fills up and stops capturing data or switches to read-only mode.

To check current Query Store storage usage:

SELECT 
    actual_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    (CAST(current_storage_size_mb AS FLOAT) / max_storage_size_mb * 100) AS percent_full,
    query_capture_mode_desc,
    size_based_cleanup_mode_desc
FROM sys.database_query_store_options;

This shows how much space Query Store is currently using, the maximum allocated space, and the percentage full.

See Microsoft’s documentation for the sys.database_query_store_options view for an explanation of the columns.

Common Scenarios

Query Store can be in different states based on storage usage. Here are some scenarios to watch for:

  • READ_WRITE with low percentage full – Query Store is operating normally with plenty of space available.
  • READ_WRITE approaching limit (80%+) – Query Store is still capturing data but getting close to the limit. Consider increasing MAX_STORAGE_SIZE_MB or enabling automatic cleanup.
  • READ_ONLY due to storage limit – Query Store has reached MAX_STORAGE_SIZE_MB and stopped capturing new data. You must either increase the limit or clear old data.
  • ERROR state – Query Store encountered an issue and isn’t functioning properly. Check SQL Server error logs for details.

Checking Storage Growth Over Time

To monitor how quickly Query Store storage is growing, you can run this query periodically and compare results:

SELECT 
    GETDATE() AS check_time,
    current_storage_size_mb,
    max_storage_size_mb,
    (CAST(current_storage_size_mb AS FLOAT) / max_storage_size_mb * 100) AS percent_full
FROM sys.database_query_store_options;

Track these values over days or weeks to estimate when you’ll hit the storage limit and need to take action.

Increasing Storage Limits

If Query Store is approaching or has reached its limit, you can increase the maximum storage size:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 5000);

That increases the maximum storage size to 5GB. Choose a size based on your available disk space and how much query history you want to retain.

Enabling Automatic Cleanup

To prevent Query Store from filling up, you can enable automatic cleanup:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

When enabled, Query Store automatically removes the oldest data when approaching the storage limit, keeping it in READ_WRITE mode instead of switching to READ_ONLY.

Adjusting Data Retention

You can also reduce how long Query Store keeps data to lower storage consumption:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE (
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14)
);

This tells Query Store to keep only 14 days of data (instead of the default of 30 days). Shorter retention periods mean less storage usage but also less historical data for analysis.

Finding the Largest Queries

To identify which queries consume the most storage:

SELECT TOP 20
    q.query_id,
    LEFT(qt.query_sql_text, 100) + '...' AS query_preview,
    LEN(qt.query_sql_text) AS query_text_length,
    COUNT(DISTINCT p.plan_id) AS plan_count,
    COUNT(rs.runtime_stats_id) AS stats_rows
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
LEFT JOIN sys.query_store_plan p ON q.query_id = p.query_id
LEFT JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY plan_count DESC, stats_rows DESC;

Queries with many plans or statistics rows tend to consume more storage. You might consider removing data for specific queries if they’re skewing your storage usage.

Setting Up Alerts

Consider setting up alerts to notify you when Query Store storage reaches critical levels:

-- Check if Query Store is over 80% full
DECLARE @percent_full FLOAT;
DECLARE @message NVARCHAR(100);

SELECT @percent_full = (CAST(current_storage_size_mb AS FLOAT) / max_storage_size_mb * 100)
FROM sys.database_query_store_options;

IF @percent_full > 80
BEGIN
    SET @message = 'Query Store is ' + CAST(@percent_full AS NVARCHAR(20)) + ' percent full';
    RAISERROR(@message, 16, 1);
END

Integrate this check into your monitoring infrastructure to catch storage issues before Query Store stops capturing data.

Recovering from Read-Only State

If Query Store has switched to read-only due to storage limits:

  1. Check current usage and determine if you can increase the limit
  2. Either increase MAX_STORAGE_SIZE_MB or clear old data with SET QUERY_STORE CLEAR ALL
  3. Enable SIZE_BASED_CLEANUP_MODE = AUTO to prevent recurrence
  4. Switch back to read-write mode: ALTER DATABASE YourDatabaseName SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

The mode change happens automatically when you increase storage limits, but you may need to manually switch back if you cleared data instead.