What benefit is there from enabling Query Store on msdb?

sql-server sql-server-2016 add tag

James Jenkins (imported from SE)

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

Top Answer

Solomon Rutzky (imported from SE)

@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][1] 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]`).
  &nbsp;

* `[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 &mdash; do not need to be fully qualified with `[master]..` &mdash; 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.


  [1]: https://topanswers.xyz/databases?q=861

Answer #2

SqlWorldWide (imported from SE)

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][1] from books online.

[MSDB Performance Tuning][2] by Geoff N. Hiten 

[The Importance of Maintenance on MSDB][3] 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.


  [1]: https://docs.microsoft.com/en-us/sql/relational-databases/databases/msdb-database
  [2]: http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx
  [3]: https://sqlperformance.com/2015/07/sql-maintenance/msdb

2 Answers