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.
Best Answer
The Query Store GUI uses this T-SQL:
The results look like:
I saw that using a T-SQL statement trace against the server while simultaneous opening the GUI for "Overall Resource Consumption" on my local SQL Server 2016 instance.
I re-factored that above query down a bit, to:
That returns a row for each day between the start end end dates provided.