在msdb上启用查询存储有什么好处？

Question

SQL系统数据库(主数据库、模型数据库、msdb查询库、tempdb查询库)的查询存储只能在msdb上使用。我查看并没有找到有关msdb上查询存储的任何文档。

虽然在GUI中看不到它，但可以在SQL 2016实例上验证它

验证查询存储已关闭

USE msdb
SELECT * FROM sys.database_query_store_options; 

打开查询存储

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

验证查询存储已打开

USE msdb
SELECT * FROM sys.database_query_store_options; 

在所有的系统数据库中，为什么msdb是唯一有选项使用Query的数据库，它添加了什么值？

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

Answer 1

Microsoft启用一个功能并不意味着它将对每个人都有用。对于系统来说，使用某些特性可能意味着依赖存储在MSDB中的信息。在这些情况下，Query可能很有用。

以下是关于MSDB数据库对象的使用和调优的几篇文章。

msdb数据库从网上图书。

MSDB性能调优作者: Geoff N. Hiten

蒂姆·拉德尼( Tim )在“MSDB维护的重要性”中提到了以下内容：

在msdb中优化索引与用户数据库一样重要。很多时候，我发现客户端正在优化用户数据库，而不是系统数据库。由于msdb数据库被Server代理、日志传送、Service、SSIS、备份和还原以及其他进程大量使用，所以索引可能变得高度分散。确保索引优化作业也包括系统数据库，或者至少包括msdb。我看到索引优化从msdb中高度分散的索引中释放了几千兆字节的空间。

我可以看到查询存储如何帮助优化索引策略和优化查询/聚集/清除存储在MSDB中的一些信息。

Answer 2

@SqlWorldWide回答了问题的“为什么是[msdb]”，所以我不会在这里重复这个问题。但要回答“为什么不[master]，[model]，[tempdb]”这一问题的一部分：

• [tempdb]是临时存储，从本质上看，无论是自动化优化还是提供历史分析的能力，似乎都无法使其受益。如果Query跟踪存储过程上的执行状态，那么当存储过程存在于其他地方时，这将于事无补。虽然可以创建临时存储过程，但是本地临时存储过程可能不会从中受益，因为它们的名称包含一个唯一的散列代码，用于在多个会话中分离类似的名称。虽然全局临时存储过程在不同会话之间有一个一致的名称，但鉴于其临时性质，无法假设跨会话具有相同名称的全局临时存储过程(假设不是同时进行)甚至具有相同的代码，因此无法具有有意义/相关的统计数据。
• [model]是创建新数据库的模板(包括[tempdb]，每次Server实例启动/重新启动时都会重新创建)。查询不会在这里执行。但是，我认为允许在这里启用Query是有意义的，以便在创建新DB时默认启用查询存储。但是，无论如何，这意味着在[tempdb]中将启用Query，这是很愚蠢的(请参阅上面的要点)。更新: Woah，耐莉！我只是重新阅读了导致这个错误的初始问题，并注意到了一些奇怪的事情:只有[master]和[tempdb]的错误消息；[model]没有报告错误。可能O.P.在复制问题时忽略了该错误消息，所以我在Server 2016 SP1-CU7-GDR (13.0.4466.4)上运行了以下命令，以便自己查看: sys.databases 模型 SET QUERY_STORE = ON；-成功完成！-重新启动实例强制重新创建诱饵；创建数据库IsQueryStoreEnabledByDefault；从sys.databases中选择* 是_查询_商店_在……上面 = 1；删除数据库IsQueryStoreEnabledByDefault；结果呢？[model]和[IsQueryStoreEnabledByDefault]是返回的，但是[tempdb]不在结果中！因此，除了前两个“然而”S之外，似乎[model]可以启用Query ( a)默认查询存储启用(是的，我甚至检查过;-)对于新创建的DB和b)在服务启动时重新创建[tempdb] (因此这不是在[tempdb]中打开它的后门)。3.
• [master]是主要的系统数据库，您不应该在这里运行代码。此外，这里存在并经常使用的存储过程要么不会从优化中受益，要么在调用它们的用户数据库上下文中执行(即从sp_开始的系统存储过程是一个特殊情况，在所有DB中它们“出现”-不需要使用[master]..完全限定--并且执行时就好像它们实际上存在于每个DB中一样)，并且可能由调用它们的用户数据库中的查询存储(S)来管理。