Yesterday I wrote about the topic SQL SERVER – Query Store Status for All the Databases. After reading the blog post, I got quite a few emails about various scripts related to the Query Store. There were two scripts that were very much asked and they are how to Turning OFF or ON Query Store for All the Database?
Honestly, there is not a magic trick by which we can turn off or on all the query stores together. Additionally, it is not a server-level setting. As a matter of fact, you need to go to every single database and turn off and turn on the query store independently. This can be very cumbersome and difficult to do if you have many multiple databases on your system.
While working with my client on Comprehensive Database Performance Health Check, we had come up with a simple script that we can use to turn on or turn off the query store for all the databases. I am sharing the scripts with you all so you can use them for the future.
Turning OFF Query Store for All the Databases
Run the following script and it will generate the script which you can run for all the databases to turn off the query store.
SELECT 'USE master;' AS Script
UNION ALL
SELECT 'ALTER DATABASE ['+name+'] SET QUERY_STORE = OFF;'
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
AND is_query_store_on = 1;
Please note there should not be any need to turn off the query store in the system database so they are excluded from the query additionally, the query is filtering out all the database where the query store is already turned off.
Once the script is generated from the example above, you can run that in the SSMS Query Window.
Turning ON Query Store for All the Databases
While turning off the query store for all the databases is simple, turning on a script is not as simple as that. When you turn on the Query Store, you should configure various options of the query store as per your business need. However, for simplicity, I am including the script here which will just turn on query store for the database with already existing options.
SELECT 'USE master;' AS Script
UNION ALL
SELECT 'ALTER DATABASE ['+name+'] SET QUERY_STORE = ON;'
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
AND is_query_store_on = 0;
When you turn on the query store by default it takes the operation mode as a Read write. If you want to explicitly specify the operation mode as well.
For Operation Mode = Read Write
SELECT 'USE master;' AS Script
UNION ALL
SELECT 'ALTER DATABASE ['+name+'] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);'
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
AND is_query_store_on = 0;For Operation Mode = Read Only
SELECT 'USE master;' AS Script
UNION ALL
SELECT 'ALTER DATABASE ['+name+'] SET QUERY_STORE = ON (OPERATION_MODE = READ_ONLY);'
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
AND is_query_store_on = 0;Summary
Well, that’s it for today. As I mentioned to you in the earlier blog post, while Query Store has many great features but if you are not using it, just go ahead and turn it off for your database to utilize resources efficiently.
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
