Last week I presented a session on Query Store and when talking about the settings I mentioned that monitoring space used by Query Store is extremely important when you first enable it for a database.  Someone asked me how I would do that and as I provided an explanation I realized that I should document my method…because I give the same example every time and I would be nice to have the code.

For those of you not familiar with the Query Store settings, please check out my post which lists each one, the defaults, and what I would recommend for values and why.  When discussing MAX_STORAGE_SIZE_MB, I mention monitoring via sys.database_query_store_options or Extended Events.  As much as I love Extended Events, there isn’t an event that fires based on a threshold exceeded.  The event related to size is query_store_disk_size_over_limit, and it fires when the space used exceeds the value for MAX_STORAGE_SIZE_MB, which is too late.  I want to take action before the maximum storage size is hit.

Therefore, the best option I’ve found is to create an Agent job which runs on a regular basis (maybe every four or six hours initially) that checks current_storage_size_mb in sys.database_query_store_options and calculates the space used by Query Store as a percentage of the total allocated, and then if that exceeds the threshold you set, send an email.  The code that you can put into an Agent job is below.  Please note you want to make sure the job runs in the context of the user database with Query Store enabled (as sys.database_query_store_options is a database view), and configure the threshold to a value that makes sense to your MAX_STORAGE_SIZE_MB.  In my experience, 80% has been a good starting point, but feel free to adjust as you see fit!

Once your Query Store size has been tweaked and stabilized, I would leave this job in place as a safety to alert you should anything change (e.g. someone else changes a Query Store setting which indirectly affects the storage used).

/* Change DBNameHere as appropriate */
USE [DBNameHere]

/* Change Threshold as appropriate */
DECLARE @Threshold DECIMAL(4,2) = 80.00
DECLARE @CurrentStorage INT
DECLARE @MaxStorage INT

SELECT @CurrentStorage = current_storage_size_mb, @MaxStorage = max_storage_size_mb
FROM sys.database_query_store_options

IF (SELECT CAST(CAST(current_storage_size_mb AS DECIMAL(21,2))/CAST(max_storage_size_mb AS DECIMAL(21,2))*100 AS DECIMAL(4,2))
FROM sys.database_query_store_options) >= @Threshold
BEGIN

     DECLARE @EmailText NVARCHAR(MAX) = N'The Query Store current space used is ' + CAST(@CurrentStorage AS NVARCHAR(19)) + 'MB
     and the max space configured is ' + CAST(@MaxStorage AS NVARCHAR(19)) + 'MB,
     which exceeds the threshold of ' + CAST(@Threshold AS NVARCHAR(19) )+ '%.
     Please allocate more space to Query Store or decrease the amount of data retained (stale_query_threshold_days).'

     /* Edit profile_name and recipients as appropriate */
     EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'SQL DBAs',
     @recipients = 'DBAs@yourcompany.com',
     @body = @EmailText,
     @subject = 'Storage Threshold for Query Store Exceeded' ;
END