SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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!

The post Cleaning up Query Store data after database restore appeared first on DBA MASTERY.

DBA Mastery

I am a very experienced multi platform Database Administrator (SQL Server, Azure SQL DB, Oracle and MySQL). I have more than 10 years of experience in the database administration field, working for large scale US based companies (multiple industries). I have multiple SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), during my spare time I like to help others using the #SQLHelp hashtag in Twitter. I hope you find all the information/DBA tips shared in this blog useful

Comments

Leave a comment on the original post [dbamastery.com, opens in a new window]

Loading comments...