sql-server add tag
user165433 (imported from SE)
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 `RESEED`ed :-( 

Do you know how clean the Query Store without `RESEED`ing the tables? 
Top Answer
Josh Darnell (imported from SE)
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][2].

`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][1]

 - 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.

[1]: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-2017#keep-the-most-relevant-data-in-query-store
[2]: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/query-store-catalog-views-transact-sql?view=sql-server-2017

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.