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!
Carlos Robles is a Solutions Architect at AWS, a former Microsoft Data Platform MVP, a Friend of Redgate, but more than anything a technology lover. He has worked in the database management field on multiple platforms for over ten years in various industries.
He has diverse experience as a Consultant, DBA and DBA Manager. He is currently working as a Solution Architect, helping customers to solve software/infrastructure problems in their on-premise or cloud environments.
Speaker, author, blogger, mentor, Guatemala SQL User group leader. If you don’t find him chatting with friends about geek stuff, he will be enjoying life with his family.