What benefit is there from enabling Query Store on msdb?

Question

Of the SQL system database (master, model, msdb, tempdb) query store can only be used on msdb. I looked and don't find any documentation about query store on msdb.

While you can't see it in the GUI, it can be validated on your SQL 2016 instance

Validate Query Store is off

USE msdb
SELECT * FROM sys.database_query_store_options; 

Turn Query Store on

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

Validate Query Store is on

USE msdb
SELECT * FROM sys.database_query_store_options; 

Of all the system database why is msdb the only one with the option to use Query Store, and what value does it add?

-- Stop Query Store
USE [master]
GO
ALTER DATABASE msdb SET QUERY_STORE = OFF
GO

Answer 1

Microsoft enabling a feature does not mean it will be useful for everyone. For systems using some of the features can mean relying on information stored in MSDB. In those cases Query Store can be useful.

Here are few articles about the usage and tuning of MSDB database objects.

msdb Database from books online.

MSDB Performance Tuning by Geoff N. Hiten

The Importance of Maintenance on MSDB by Tim Radney where he mentioned following:

Optimizing indexes in msdb is just as important as your user databases. Many times I have found clients who are optimizing user databases but not the system databases. Since the msdb database is heavily used by SQL Server Agent, Log Shipping, Service Broker, SSIS, backup and restore, and other processes, the indexes can get highly fragmented. Ensure that your index optimization jobs also include your system databases, or at least msdb. I’ve seen index optimizations free up several gigabytes of space from highly fragmented indexes within msdb.

I can see how query store can help in optimizing your indexing strategy and optimally querying/aggrgating/purging some of the information stored in MSDB.

Answer 2

@SqlWorldWide answered the "why [msdb]" part of the question so I won't duplicate that here. But to answer the "why not [master], [model], [tempdb]" part of the question:

• [tempdb] is temporary storage and by its very nature would not seem to ever benefit from either automated optimization or the ability to provide historical analysis. If Query Store tracks execution stats on Stored Procedures, that won't help here when the Stored Procedures exist elsewhere. And while it is possible to create temporary Stored Procedures, local temporary Stored Procs would likely not benefit from this given that their name includes a unique hash code to separate similar names across multiple sessions. And while global temporary Stored Procs have a consistent name across sessions, given the temporary nature, there is no way to assume that global temporary Stored Procs of the same name across sessions (assume not at the same time) will even have the same code, and hence cannot have meaningful / correlatable statistics.

• [model] is the template for creating new databases (including [tempdb], which gets re-created each time the SQL Server instance starts / restarts). Queries do not execute from here. However, I suppose it might make sense to allow Query Store to be enabled here so that it is ON by default when creating new DBs. But, however to that however, that would mean Query Store would be enabled in [tempdb], and that is just silly (see point directly above).

  UPDATE:
  Woah, Nelly! I just re-read the initial question that lead to this one and noticed something strange: there were only error message for [master] and [tempdb]; there was no error reported for [model]. It is possible that the O.P. simply left out that error message when copying into the question, so I ran the following on SQL Server 2016 SP1-CU7-GDR (13.0.4466.4) to see for myself:

  ALTER DATABASE [model] SET QUERY_STORE = ON; -- completes successfully!
  
  -- Restart instance to force recreation of [tempdb];
  
  CREATE DATABASE [IsQueryStoreEnabledByDefault];
  
  SELECT * FROM sys.databases WHERE [is_query_store_on] = 1;
  
  DROP DATABASE [IsQueryStoreEnabledByDefault];
  

  And the results? [model] and [IsQueryStoreEnabledByDefault] are returned, but [tempdb] is not in the results! So, an additional however to the first two "however"s, it seems that [model] can have Query Store enabled which a) defaults Query Stored enablement (yes, it's a word, I even checked ;-) for newly created DBs, and b) is ignored for the re-creation of [tempdb] upon the service starting (hence this is not a back-door for turning it on in [tempdb]).  

• [master] is main system Database and you should not have code running here. Also, the Stored Procedures that exist here and are frequently used either would not benefit from optimization, or execute in the context of the User Database where they are invoked (i.e. system stored procs starting with sp_ are a special case where they "appear" in all DBs — do not need to be fully qualified with [master].. — and execute as if they actually exist in each DB) and are probably governed by Query Store in the User Database(s) where they are being invoked.