Query Store Disabled, Misconfigured, Failed or Close to Failing

Added in SQL Server 2016, the Query Store feature captures data about query plans and plan selection, plus many other metrics to make performance troubleshooting and analysis easier. By default, the Query Store is disabled and must be manually enabled for each database.

Query Store is not enabled

The Query Store is not enabled for this database.

Suggested Action

Enable the Query Store for all databases.

Query Store is close to the size limit

The Query Store is close to reaching the size limit set by the MAX_STORAGE_SIZE_MB option. When the max Query Store size is reached, the Query Store automatically transitions to read-only and stops collecting new data. Old data is still accessible, but the Query Store will not collect new data until it is set back to read-write. The most common cause is the default value of 100MB, which is often too small for busy systems or when longer storage periods are required.

Suggested Action

Increase the MAX_STORAGE_SIZE_MB by 20% and monitor.

Query Store has failed due to exceeding the size limit

The Query Store has reached the size limit set by the MAX_STORAGE_SIZE_MB option. When the max Query Store size is reached, the Query Store automatically transitions to read-only and stops collecting new data. Old data is still accessible, but the Query Store will not collect new data until it is set back to read-write. The most common cause is the default value of 100MB, which is often too small for busy systems or when longer storage periods are required.

Suggested Action

Increase the MAX_STORAGE_SIZE_MB by 20% and monitor.

Query Store has encountered internal errors and failed

The Query Store state is set to ERROR due to encountering internal errors.

Suggested Action

Run sp_query_store_consistency_check to recover the Query Store.

Query Store Capture Mode is not set to AUTO

If capture mode is set to ALL, many insignificant queries will be captured by the query store, such as one use queries and queries with very short runtimes. If capture mode is set to NONE, then the query store is not capturing any new queries. When the capture mode is set to AUTO, query capture is optimized so that insignificant queries are ignored. Thresholds for properties such as execution count, compile and runtime duration are considered during the internal selection process.

Suggested Action

Set Query Store Capture Mode to AUTO.

The retention period for stale queries within the Query Store exceeds 14 days

The Stale Query Threshold controls the retention period of inactive queries and their runtime statistics for a set number of days. Reducing this value will help avoid keeping historical data that has no planned use and improve query store speed and issue detection.

Suggested Action

Reduce the Stale Query Threshold to less than 14 days.

Any suggestion from Aireforge should be thoroughly tested before being implemented into production.

Further Reading

Monitoring performance by using the Query Store | Microsoft Docs

Best practices with Query Store | Microsoft Docs