Query store – bumping up max_storage_size_mb (Query Store Retention, Max Size) works for a short while then goes back to 250?

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.

Method 1

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

This is the output I got when doing the changes using the GUI
Query store - bumping up max_storage_size_mb (Query Store Retention, Max Size) works for a short while then goes back to 250?

Method 2

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.

Method 3

Triggers

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)[1]', 'nvarchar(4000)') 
              );

      IF UPPER(@command) LIKE '%SET QUERY_STORE%'
      BEGIN
      
          INSERT 
              master.dbo.trigger_log
          (
              PostTime,
              LoginName,
              CommandText
          )
          SELECT
              PostTime = 
                  x.c.value('(PostTime)[1]', 'datetime'),
              LoginName = 
                  x.c.value('(LoginName)[1]', '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:

Query store - bumping up max_storage_size_mb (Query Store Retention, Max Size) works for a short while then goes back to 250?

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply