All we need is an easy explanation of the problem, so here it is.
I’ve been unsuccessful doing this a few times now. In the UI I’m able to successfully bump up the size of the query store in Query Store Retention setting from 250 to 1000 mb. I make the change, close the database properties window, reopen, and it looks like the change was successful, shows 1000 mb.
Then when I look at it again (maybe the next day or so) it’s reverted back to 250 mb.
The version is Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) – 14.0.3048.4.
What could be happening here?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
I agree with Josh Darnell that you should set up an Extended event session but I recommend a more specific event. I have used this recently to troubleshoot the Query store.
CREATE EVENT SESSION [QueryStoreSettings] ON SERVER ADD EVENT qds.query_store_db_settings_changed ( ACTION ( sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.server_instance_name, sqlserver.sql_text, sqlserver.username ) ) ADD TARGET package0.event_file(SET filename=N'QueryStoreSettings',max_file_size=(5),max_rollover_files=(10)) WITH (STARTUP_STATE=OFF) GO ALTER EVENT SESSION [QueryStoreSettings] ON SERVER STATE = START; GO
The setting shouldn’t change back on its own.
You can see who or what is changing it, and when, by setting up an Extended Events session like this one:
CREATE EVENT SESSION [alter_statements] ON SERVER ADD EVENT sqlserver.object_altered ( ACTION(sqlserver.nt_username,sqlserver.server_principal_name) WHERE ([object_name]=N'QDSTest') ) ADD TARGET package0.event_file (SET filename=N'alter_statements') WITH (STARTUP_STATE=OFF) GO ALTER EVENT SESSION [alter_statements] ON SERVER STATE = START; GO
You would need to replace "QDSTest" with the database name of interest in your case.
With that you should be able to find the username associated with the session that issued the
ALTER TABLE statement that changed your query store settings, which will hopefully be helpful in tracking down who or what is changing that.
One possibility is that you have automated deployments to this database that use a model-based approach (like SQL Server Data Tools dacpac deployments). If the "model" has query store configured a certain way, each deployment will try to change the settings back to what is in the model.
One alternative would be to create a trigger to catch and log the data, and then review the data after you note a change.
First, you’ll need a table:
CREATE TABLE master.dbo.trigger_log ( id int PRIMARY KEY IDENTITY, PostTime datetime, LoginName sysname, CommandText nvarchar(4000) );
Next, you’ll need a trigger:
CREATE OR ALTER TRIGGER [query_store_shenanigans] ON ALL SERVER FOR ALTER_DATABASE AS SET NOCOUNT, XACT_ABORT ON; BEGIN DECLARE @command nvarchar(4000) = ( SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)', 'nvarchar(4000)') ); IF UPPER(@command) LIKE '%SET QUERY_STORE%' BEGIN INSERT master.dbo.trigger_log ( PostTime, LoginName, CommandText ) SELECT PostTime = x.c.value('(PostTime)', 'datetime'), LoginName = x.c.value('(LoginName)', 'nvarchar(128)'), CommandText = @command FROM ( SELECT event_data = EVENTDATA() ) AS e CROSS APPLY e.event_data.nodes('/EVENT_INSTANCE') AS x(c); END END; GO
And a command to test the logging:
ALTER DATABASE StackOverflow SET QUERY_STORE ( WAIT_STATS_CAPTURE_MODE = ON );
A query to review the log table:
SELECT tl.* FROM master.dbo.trigger_log AS tl;
Here are the results I get locally after running some variations on the command:
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂