Cleaning up Query Store data after database restore

Query Store is a very powerful tool for performance tuning, if you are not using it already you should!! It will save you a lot of time and effort because it makes the performance troubleshooting easy for DBA’s. It helps you to quickly find performance differences caused by query plan change or a query regression after an upgrade.

One of the caveats of using this tool is that uses space from the PRIMARY file group when enabled, that means if the database is restored to another environment the Query Store data will be restored as well making your database restored version a little bit bigger and containing information that is probably not relevant for a development environment (unless you are doing a troubleshooting).

In case you have recurrent tasks to restore a production database in a different environment and you are not interested on having the Query Store data on your restored version, there is a way to cleanup the Query Store data for good.

Here is the T-SQL script you have to use:

ALTER DATABASE "MyDB" SET QUERY_STORE CLEAR;
ALTER DATABASE "MyDB" SET QUERY_STORE (OPERATION_MODE = READ_ONLY);
ALTER DATABASE "MyDB" SET QUERY_STORE = OFF;

As you can see, with this simple T-SQL script you are not only cleaning up all the Query Store data but also disabling Query Store putting it in read only mode.

Stay tuned for more DBA mastery tips!

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.