SQL Server中查询存储的用例

Query store was introduced in SQL Server 2016. It is often referred to as a “flight data recorder” for SQL Server. Its main function is that it captures the history of executed queries as well as certain statistics and execution plans. Furthermore, the data is persistent, unlike the plan cache in which the information is cleared upon a server restart or reboot. You can customize, within Query Store, how much and how long the query store can hold the data.

查询存储是SQL Server 2016中引入的。它通常被称为SQL Server的“飞行数据记录器”。 它的主要功能是捕获执行查询的历史记录以及某些统计信息和执行计划。 此外，数据是持久性的，与计划缓存不同，在计划缓存中，信息会在服务器重启或重新启动时清除。 您可以在查询存储中自定义查询存储可以保存数据的数量和时间。

This feature is enabled on the database level. Though, I have strongly urged many to enable this by default by enabling it in the model database. That way every time you create a new database on a particular instance, query store will already be running.

在数据库级别启用此功能。 但是，我强烈建议许多人通过在模型数据库中启用它来默认启用它。 这样，每次在特定实例上创建新数据库时，查询存储将已经在运行。

The query store is made up of 3 components.

查询存储由3个组件组成。

• Plan store for holding execution plan information
  计划存储，用于保存执行计划信息
• Runtime stats for holding statistical information
  用于保存统计信息的运行时统计信息
• Wait for stats for holding wait statistics information
  等待统计信息以保存等待统计信息

It’s great to have all this information in one location as opposed to gathering it separately with custom scripts.

最好将所有这些信息都放在一个位置，而不是使用自定义脚本分别收集这些信息。

The remainder of this article will present some use cases for query store. It isn’t meant to be overly technical (we can save that for another post). Rather, it will give us good talking points, especially when it comes to justifying to management why your environment could use this feature.

本文的其余部分将介绍查询存储的一些用例。 这并不意味着技术过高（我们可以将其保存在另一篇文章中）。 而是，它将为我们提供良好的论点，尤其是在向管理层证明您的环境可以使用此功能的理由时。

为什么要使用查询存储？ (Why should I use the Query Store?)

Query store can be used for a number of things.  For example:

查询存储可用于许多事情。 例如：

• Find out what was happening on my server last night?
  找出昨晚服务器上发生的情况？
• Identify and fix queries suffering from parameter sniffing or plan regression
  识别并修复遭受参数嗅探或计划回归的查询
• Testing (ie – when upgrading to a new version of SQL server, or development projects)
  测试（即–升级到新版本SQL Server或开发项目时）

昨晚我的服务器上发生了什么事？ (What happened on my server last night?)

Query store is great for a historical view of what your server was doing. It’s often referred to as “a flight data recorder”.

查询存储非常适合用于服务器历史记录的历史记录。 它通常被称为“飞行数据记录器”。

There were so many times where I was asked “what happened to so and so’s process last night? why did it run so long etc…”

有很多次我被问到“昨晚某事发生了什么？ 它为什么运行了这么长时间等等？”

Query store would have saved me a ton of time to find out what was going on and ideas on how to correct it. Not only that, but the fact that we can identify it so quickly means that we can prevent this from happening again.

查询存储将为我节省大量时间，以了解发生了什么事情以及有关如何纠正它的想法。 不仅如此，而且我们能够如此Swift地识别它的事实意味着我们可以防止这种情况再次发生。

Once the query store is enabled you can look at the reports in the “query store” section of the databases. Please reference the image below.

启用查询存储后，您可以在数据库的“查询存储”部分中查看报告。 请参考下图。

识别并修复遭受参数嗅探或计划回归的查询： (Identify and Fix queries suffering from Parameter sniffing or Plan regression:)

There are times when the Query Optimizer may choose a different plan due to certain values for parameters. And in rare cases, this new plan is not optimal for most inputs and thus gives us degraded performance. 

有时由于某些参数值，查询优化器可能会选择其他计划。 而且在极少数情况下，此新计划对于大多数输入而言并非最佳选择，因此会降低性能。

In the past, this was difficult to identify and remedy. Query store has empowered us as DBAs and developers. Utilizing the “Regressed Queries” option from SQL Server Management Studio can be used as a starting point for analysis. Now you have what SQL has identified as queries in which the execution metrics have been degraded.

过去，这很难确定和补救。 查询存储使我们成为DBA和开发人员。 利用SQL Server Management Studio中的“回归查询”选项可以用作分析的起点。 现在，您已经拥有SQL所标识的查询，其中执行指标已降级。

The graphical user interface makes it easy to see the multiple plans and which are more resource intensive. This visualization gives a starting point. From here you can see actual execution plan and make an educated decision as to which one you want to force (or not).

图形用户界面使您可以轻松地查看多个计划，并且这些计划的资源消耗更大。 该可视化提供了一个起点。 在这里，您可以查看实际的执行计划，并根据自己的意愿决定要（或不）强制执行哪一个。

In order to force a plan, it’s as simple as selecting the query plan and pressing the “force plan” button highlighted below.

为了强制执行计划，就像选择查询计划并按下下面突出显示的“强制计划”按钮一样简单。

测试中 (Testing)

There are actually a couple of scenarios in regard to testing. One would be for an upgrade or migration project. Another could be for a development project. 

关于测试，实际上有两种方案。 一个将用于升级或迁移项目。 另一个可能是用于开发项目。

With a migration, you can do a dry run and then run a workload against a database with query store enabled in the old compatibility mode. Once that information is collected, set the databases compatibility mode to the latest and continue testing. Check the query store to see if there have been any plan regression and tune accordingly.

通过迁移，您可以进行空运行，然后针对在旧兼容模式下启用了查询存储的数据库运行工作负载。 收集到该信息后，将数据库兼容模式设置为最新并继续测试。 检查查询存储以查看是否存在任何计划回归并进行相应调整。

The approach might look something like this:

该方法可能看起来像这样：

• Migrate the database to a new version of SQL server. But leave the compatibility level under the older version of SQL
  将数据库迁移到新版本SQL Server。 但是将兼容性级别保留在旧版本SQL下
• Enable query store on said database
  在所述数据库上启用查询存储
• Run workload against the database and let query store collect information. (make sure query store is sized properly depending on how much you think you need)
  针对数据库运行工作负载，并让查询存储收集信息。 （确保查询存储的大小取决于您认为所需的大小）
• Set the compatibility level for this database to the latest version of SQL Server
  将此数据库的兼容性级别设置为SQL Server的最新版本
• Check query store for any query regressions. Once you’ve identified anything, make sure to document it
  检查查询存储中是否有任何查询回归。 确定任何内容后，请确保将其记录下来
• From there either force plans or tune the query to run more efficiently
  从那里强制计划或调整查询以更有效地运行

Look at the top consuming queries and see if you want to tune them or force a plan or just gather a baseline of statistics and execution plans for the major store procedures and queries.

查看消耗最大的查询，查看是否要调整它们或强制执行计划，还是只是收集主要商店过程和查询的统计数据和执行计划的基准。

Documentation is important, and we don’t do it enough. Gathering as much baseline information as possible early on will help tremendously as your database and workload increase over time.

文档很重要，我们做得还不够。 随着数据库和工作负载的不断增加，尽早收集尽可能多的基准信息将大有帮助。

You can utilize query store to analyze resources such as CPU, I/O and memory at the database level. This will allow you to see the patterns early on see how it either deviates over time or if there is a onetime occurrence that shows a change in the pattern.

您可以利用查询存储在数据库级别分析资源，例如CPU，I / O和内存。 这样一来，您可以及早查看模式，以了解其随着时间的变化或一次出现的情况表明模式发生了变化。

This is an example of doing our due diligence as we prepare to migrate or upgrade databases to new versions of SQL. Take advantage of it, document it, and present it to management.

这是在准备将数据库迁移或升级到新版本SQL时进行尽职调查的一个示例。 利用它，记录下来，并提交给管理层。

As with the above paragraph, the same approach can be used when working on a development project. You can identify queries, parameter sniffing ahead of time and either tune the queries or force the plan before the go-live date. 

与上面的段落一样，在开发项目中可以使用相同的方法。 您可以识别查询，提前进行参数嗅探，或者调整查询或在上线日期之前强制计划。

查询所有商店？ (Query store for all?)

One disadvantage of Query store is that it is only available for SQL Server 2016 and later versions. But thanks to the Open Query store project, developed by William Durkin, this functionality has been made available to everyone on SQL Server 2008 and higher.

查询存储的一个缺点是它仅适用于SQL Server 2016和更高版本。 但是感谢William Durkin开发的Open Query store项目 ，此功能已在SQL Server 2008及更高版本上的每个人使用。

Open Query store functionality is also included in ApexSQL Plan, a new, free tool for query plan analysis.

开放查询存储功能也包含在ApexSQL Plan中 ， ApexSQL Plan是用于查询计划分析的新的免费工具。

结论： (Conclusion:)

I hope that you found this article informative. It was not meant to be overly technical, rather it’s intent was to give some ideas on how you can utilize query store in your environment. This is especially useful if you must bring it up to management so that they know how useful it will be to you as a DBA and in turn, how it will impact the business.

我希望您发现这篇文章有益。 这并不是要过分技术，而是要就如何在环境中利用查询存储给出一些想法。 如果您必须将其引入管理层，以使他们知道作为DBA的身份对您有多大用处，进而对您的业务有何影响，这将特别有用。

翻译自: https://www.sqlshack.com/use-cases-for-query-store-in-sql-server/