Don't RESEED tables on Query Store Clean 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?
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 &gt; 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.