We are using Query Store. And now we have solved one problem with it by cleaning the Query Store every night. And when you clean the Query Store, the Query Store tables are being RESEEDed :-(

Do you know how clean the Query Store without RESEEDing the tables?

up vote 9 down vote accepted

Assuming you're using the command ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR to "clean the query store," I decided to see what queries are run behind the scenes when that command executes.

To that end, I set up a basic Extended Events session by using the "Query Detail Tracking" XE template that's included with SSMS. This captures several events that should cover most queries running on a system:

  • error_reported
  • module_end
  • rpc_completed
  • sp_statement_completed
  • sql_batch_completed
  • sql_statement_completed

By default it excludes queries running in the context of the system databases (database_id > 4), which is fine for this test since query store lives within the user database where it's enabled.

After starting that XE session, I ran the ALTER DATABASE...SET QUERY_STORE CLEAR command, and the following queries were picked up by the session:

TRUNCATE table sys.plan_persist_runtime_stats;
TRUNCATE table sys.plan_persist_runtime_stats_interval;
TRUNCATE table sys.plan_persist_plan;
TRUNCATE table sys.plan_persist_query;
TRUNCATE table sys.plan_persist_query_text;

Adding the optional "ALL" parameter after "CLEAR" ran this additional statement:

TRUNCATE table sys.plan_persist_context_settings;

You may notice that these are the base tables for the Query Store catalog views.

TRUNCATE TABLE resets identity values:

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

So this behavior is "by design" - you can't depend on the identity values in the query store tables across "query store clear" operations.

What problem are you solving by clearing the query store? Perhaps we could suggest a different option if you provide that context in a new question.

Some potential options for removing some queries without truncating the tables are found here:

Best Practice with the Query Store - Keep the most relevant data in Query Store

  • Configure time-based policy to activate auto-cleanup
  • Activate size-based cleanup policy
  • Configure Query Capture Mode to Auto

The first two options cause rows in the Query Store to be deleted by id based on their age and / or how expensive the queries were.

The second option limits the amount of data being added to the Query Store in the first place.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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