Sql-server – Enable query store for all databases

query-storesql serversql-server-2016

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)

Best Answer

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.

Related Question