Are Changes to Query Store Logged?

The Query Store feature is a bit unique in that its status can change without user interference, which means it is important to understand how changes to Query Store are logged.  A couple weeks ago John Deardurff posted a question on Twitter asking specifically whether the SQL Server ERRORLOG is written to when the OPERATION_MODE changes to READ_ONLY because  MAX_STORAGE_SIZE_MB is exceeded.  I had never tested to confirm, but I know there is an event in Extended Events that will fire when the limit is reached.  I also know that when a user makes a change to a Query Store setting, it is logged in the ERRORLOG.

User Changes

With a copy of WideWorldImporters restored in my SQL Server 2019 VM, I will create an Extended Events with a few events I think will be relevant:

CREATE EVENT SESSION [XEStatusChange]
ON SERVER
ADD EVENT qds.query_store_db_settings_changed,
ADD EVENT qds.query_store_disk_size_info,
ADD EVENT qds.query_store_disk_size_over_limit
ADD TARGET package0.event_file(SET filename=N’C:\temp\XEStatus’,max_file_size=(256))
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE,  TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);
GO

Next, we will change the MAX_STORAGE_SIZE_MB to just 5MB.  Note that this is an extremely low value that I do not recommend.

USE [master];
GO
ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
MAX_STORAGE_SIZE_MB = 5
);
GO

If we look at the output from Extended Events, we can see that my change to Query Store is logged:

If we check the ERRORLOG, information about the change exists there as well:

Finally, a quick peek of the Query Store pane in SSMS confirms the settings:

Another quick note: these are not the values I would recommend for a production system.  For more information on settings, check out my Query Store Settings post.

System Changes

Now we will explore what gets logged when the OPERATION_MODE setting changes from READ_WRITE to READ_ONLY.  We will introduce this by running a very ad hoc workload that generates thousands of queries with different literal values.  The code exists in the post, Examining the Performance Impact of an Ad Hoc Workload, and we will run usp_RandomSelects from multiple threads with the Extended Events session still running.

Within a few minutes, the following events show up in the Live Data View:

If we check the ERRORLOG, there are no new entries related to Query Store settings.

Summary

As you can see, changes to Query Store are logged differently, depending on the type of change.  Any Query Store option that is changed by a user using the ALTER DATABASE command is captured in the ERRORLOG.  The query_store_db_settings_changed event also fires. When the OPERATION_MODE is changed by the system, because the storage allocated to Query Store is exceeded, the change is not captured in the ERRORLOG, but it is captured with the query_store_disk_size_over_limit event. In addition, the query_store_disk_size_info event, which fires on a regular basis, will also report the max and current size values. If you want to know when Query Store changes to a READ_ONLY state, you will need to set up an Extended Events session to capture it. To be more proactive, I recommend setting up an Agent job that runs on a regular basis to monitor the space used, and sends an email if it exceeds a certain percentage.