Adding the optional “ALL” parameter after “CLEAR” caused this additional statement to show up:

TRUNCATE table sys.plan_persist_context_settings;

What does it all mean

Presumably those are the base tables that underlie the Query Store catalog views. Thus, as the MS Docs very tersely explained, all of the data collected by the Query Store is deleted when you run this command.

In the case of the Stack Exchange question mentioned at the beginning of this post, the use of TRUNCATE (vs DELETE) under the covers explains why their Query Store-related identity fields were being “reseeded,” since TRUNCATE TABLEresets identity values:

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column.

Regarding the extra query run by the “ALL” command, I’m guessing the number of distinct context settings in a given environment is usually pretty small (as a given client generally uses the same context settings each time), so Microsoft opted to leave them as a minor performance optimization. But that’s just a guess.

Other ways to trim the Query Store

The MS Docs article Best Practice with the Query Store has a section on this titled “Keep the most relevant data in Query Store.” Rather than manually clearing all the data, there are two main options trimming the data here.

The first is setting the time-based automatic cleanup option, which runs once every 24 hours and removes query data older than a set threshold:

You can have both of these set of you want. In both cases, they delete the oldest queries first.

The difference in these cleanup processes is that they use delete rather than truncate, so identity values won’t be reset. If you happen to capture this process in action, you’ll see a bunch of queries like this:

DELETE sys.plan_persist_runtime_stats WHERE plan_id IN (-- etc, you get it)

Changes in SQL Server 2017

Starting today in Azure SQL Database and from CTP 2.0 of SQL Server 2017 wait stats per query are available in Query Store.

This added an additional catalog view called sys.query_store_wait_stats. Running QUERY_STORE CLEAR [ALL] on a 2017 instance executes one more truncate statement:

TRUNCATE table sys.plan_persist_wait_stats;

Summary

Clearing the Query Store uses TRUNCATE and will thus reset the identity fields on those underlying tables. You’re probably better off using the automatic time or size-based cleanup options. I hope you all found this little curiosity interesting as well!