Enable query store for all databases

sql-server sql-server-2016 add tag

James Jenkins (imported from SE)

I want to start using Query Store with [suggested setting by Erin Stellato](https://www.sqlskills.com/blogs/erin/query-store-settings/).

    USE [master]
    GO
    ALTER DATABASE DatabaseName SET QUERY_STORE = ON
    GO
    ALTER DATABASE DatabaseName SET QUERY_STORE (OPERATION_MODE = READ_WRITE
    , INTERVAL_LENGTH_MINUTES = 30
    , MAX_STORAGE_SIZE_MB = 1000
    , QUERY_CAPTURE_MODE = AUTO)
    GO

But that is a by database setting, and I want to be able to activate on all the databases of an instance at once.  

I found [SET RECOVERY Model Using sp_msforeachdb (at serverfault)](https://serverfault.com/questions/394451/set-recovery-model-using-sp-msforeachdb)

I can use the OP's solution 

    EXECUTE master.sys.sp_MSforeachdb 'USE [?];
    IF DB_ID() >= 5
    ALTER DATABASE [?] SET QUERY_STORE = ON
    ALTER DATABASE [?] SET QUERY_STORE (OPERATION_MODE = READ_WRITE
    , INTERVAL_LENGTH_MINUTES = 30
    , MAX_STORAGE_SIZE_MB = 1000
    , QUERY_CAPTURE_MODE = AUTO)
    '
While it works I get the errors 

> Msg 12438, Level 16, State 1, Line 41

> Cannot perform action because Query Store cannot be enabled on system database master.

> Msg 5069, Level 16, State 1, Line 41

>ALTER DATABASE statement failed.

>Msg 12438, Level 16, State 1, Line 41

>Cannot perform action because Query Store cannot be enabled on system database tempdb.

>Msg 5069, Level 16, State 1, Line 41

>ALTER DATABASE statement failed.


I tried several variations of the solution by [Jason Cumberland](https://serverfault.com/questions/394451/set-recovery-model-using-sp-msforeachdb/394756#394756) but could not get it working. 

    set quoted_identifier on

    EXECUTE master.sys.sp_MSforeachdb '
    IF '?' not in ('tempdb','master','model')
    begin
        exec (ALTER DATABASE [?] SET QUERY_STORE = ON
    ALTER DATABASE [?] SET QUERY_STORE (OPERATION_MODE = READ_WRITE
    , INTERVAL_LENGTH_MINUTES = 30
    , MAX_STORAGE_SIZE_MB = 1000
    , QUERY_CAPTURE_MODE = AUTO))
    end
    '
I am getting the error 

> Msg 102, Level 15, State 1, Line 25

> Incorrect syntax near '?'.

These two work

      --Turn off on all databases
      EXECUTE master.sys.sp_MSforeachdb 'USE [?]; ALTER DATABASE [?] SET QUERY_STORE = OFF'

and 

    --Check all Query Store database settings at once
    EXECUTE master.sys.sp_MSforeachdb 'USE [?]; SELECT * FROM sys.database_query_store_options'

**Question: How can I turn on Query Store on all the database of an instance without getting an error message?**

Note: of the system databases msdb is the only one that that allows query store.  I am not excluding it currently.

I am using SSMS 17.4 against, SQL instance running SQL 2016 (SP1)

Top Answer

Solomon Rutzky (imported from SE)

In both cases for the attempted queries in the question, the problems are simply syntactical.

In the first case: You have an `IF` condition, but do not group the statements after it together, so only the statement immediately following the `IF` is conditional. The second `ALTER DATABASE` statement always executes. To fix, do this:

    EXECUTE master.sys.sp_MSforeachdb N'USE [?];
    IF DB_ID() >= 5
    BEGIN
      ALTER DATABASE [?] SET QUERY_STORE = ON;
      ALTER DATABASE [?] SET QUERY_STORE
       (OPERATION_MODE = READ_WRITE,
        INTERVAL_LENGTH_MINUTES = 30,
        MAX_STORAGE_SIZE_MB = 1000,
        QUERY_CAPTURE_MODE = AUTO);
    END;
    ';

You probably also no not need the `USE` statement. It could be removed and then the `DB_ID()` could be changed into `DB_ID(N''?'')`.

In the second case: You did not escape the embedded single-quotes. You also had an extraneous `EXEC`, and you did not include the `msdb` system database. To fix all of that, do the following:

    EXECUTE master.sys.sp_MSforeachdb N'
    IF (N''?'' NOT IN (N''tempdb'', N''master'', N''model'', N''msdb''))
    BEGIN
       ALTER DATABASE [?] SET QUERY_STORE = ON;
       ALTER DATABASE [?] SET QUERY_STORE
        (OPERATION_MODE = READ_WRITE,
         INTERVAL_LENGTH_MINUTES = 30,
         MAX_STORAGE_SIZE_MB = 1000,
         QUERY_CAPTURE_MODE = AUTO);
    END;
    ';

**PLEASE NOTE:** @Kris is not incorrect in saying that "`sp_MSForeachDB` is undocumented and unsupported", and the advice to use another mechanism to cycle through DBs is good advice. It might not be worth the trouble if this is a one-time task, but if this code is to be used repeatedly, then yes, you should probably heed that warning.

Answer #2

Kris Gruttemeyer (imported from SE)

First, `sp_MSForeachDB` is undocumented and unsupported, so stop using that.  I've actually seen it miss DBs completely in a real-world scenario.

Aaron Bertrand [has a much better version that will not miss DBs][2], I've been using it for quite some time with no issues.

If you don't feel like cursoring through the DBs, you could always go with good old fashioned Dynamic SQL to generate your script and then run it manually:

        SELECT 'ALTER DATABASE '
        +QUOTENAME(name)+ 
        ' SET QUERY_STORE = ON;' 
    FROM sys.databases  where database_id>4



  [1]: https://dba.stackexchange.com/users/1186/aaron-bertrand
  [2]: https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-sp_msforeachdb

2 Answers