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 a Microsoft Data Platform MVP also a very experienced multi platform DBA (MySQL, Oracle, SQL Server, SQL Azure) with over 10 years of experience in the database field.
I work in database support as primary consultant and DBA manager for larger US based companies in the healthcare, insurance, retail, food and energy industries.
I have a few certifications under my belt MCSE, MCSA, MCP, MCTS, ITIL v3, Docker, Kubernetes and RedHat openshift.
International speaker, blogger, Guatemala SQL community group leader, mentor.
Love everything related to technology, sci-fi and video games.