Enable query store for all databases

Question

I want to start using Query Store with suggested setting by Erin Stellato.

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)

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 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)

Answer 1

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

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, 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