Query Store status

Job details

Name:	Query Store status
Platform:	Sqlserver
Category:	Capacity
Description:	Checks Query Store space usage in every database where it is enabled.
Long description:	Checks Query Store space usage in every database where it is enabled by analyzing statistic from sys.database query store_options system catalog view.
Version:	1.32
Default schedule:	30 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2014′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
ignore databases	model	List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
min histr interval	4	The minimum number of hours before a new history record will be inserted into the history table.
warning threshold	90	The maximum percentage of space usage allowed for Query Store before a warning is triggered.
alarm threshold	95	The maximum percentage of space usage allowed in the Query Store before an alarm is triggered.
history threshold	30	The number of days the statistics are kept in the history table.
return status when READ ONLY	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the Query Store stops (gets in READ ONLY mode)

Job Summary

Purpose: Monitors and analyzes the Query Store space usage on every SQL Server database where the Query Store feature is enabled.
Why: This monitoring is crucial as it helps maintain the performance of the Query Store by avoiding excessive space usage that can lead to decreased query performance and possible database disruptions. The job alerts when space usage thresholds are reached.
Manual checking: To manually check the status of the Query Store, one could use the following system views:

SELECT actual_state_desc, current_storage_size_mb, max_storage_size_mb, readonly_reason FROM [YourDatabaseName].sys.database_query_store_options;

Task Procedures and Operations

The monitoring job performs several key operations:

Creates necessary tables to hold the Query Store status and statistics:
- “dbw_query_store_tab” for current status
- “dbw_query_store_histr_tab” for historical data
- “dbw_query_store_stat” for temporary statistics
A stored procedure “dbw_query_store_status_proc” is used to:
- Fetch configurations from DBWatch parameters
- Calculate the current and maximum storage used by Query Stores across databases
- Insert new records into the history table based on a minimum historical interval
- Update the status based on various thresholds for warnings and alarms
- Handle exceptions and log details for troubleshooting

Dependency Management

The job has dependencies to ensure data integrity and proper cleanup in failure scenarios:

Object	Type	Description
dbw_query_store_status_proc	Procedure	Main procedure checking Query Store status
dbw_query_store_tab	Temporary Table	Holds the current status of the Query Store
dbw_query_store_histr_tab	Temporary Table	Holds historical Query Store usage statistics
dbw_query_store_stat	Temporary Table	Temporary table for ongoing statistics

Reporting and Alerts

Reports generated by the monitoring job provide insights into:

Current and historical usage statistics of Query Stores.
Alert conditions based on defined thresholds.
Specific conditions causing the Query Store to switch to a read-only state.

The job defines two essential reports:

Query Store Status: Showcasing the enabled databases with their respective Query Store statuses and the percentage of space used.
Query Store Storage History: Displaying historical data about space usage, helping in trend analysis and capacity planning.

Describing these components provides a clear understanding of how the monitoring job functions, the critical dependencies involved, and the significant role of reporting in ongoing database management.