Sql-server – What benefit is there from enabling Query Store on msdb

msdbquery-storesql serversql-server-2016

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

Best Answer

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.

Related Question