3

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?

1
  • 1
    Are you sure the SQL server doesn't crash and restart in the meantime?
    – fraxinus
    Aug 10 '21 at 8:53
3

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 enter image description here

2
  • Very nice, I wasn't aware of that event! Aug 10 '21 at 15:50
  • 1
    Thank you, this is outstanding info! I set up this session, changed the Query store max size to my desired size in the UI, and then saw that it was reset to 250. Through the app name figured out what specifically was resetting it. It turns out that there was a job running every hour resetting Query Store and turning on deadlock flags. I guess there are some circumstances in which query store gets turned off (I've seen this on another server, didn't dig into it - maybe a server restart?), so somebody before my time set this up.
    – Sylvia
    Aug 11 '21 at 18:11
5

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.

5

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:

NUTS

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.