SQL Server 2017中的自适应查询处理

The SQL Server Query Processing Engine is the most complex and sophisticated part of SQL Server. This engine has many responsibilities, but one of the most important parts is to design the query execution plan and execute it in a best and most efficient possible manner.

SQL Server查询处理引擎是SQL Server最复杂，最复杂的部分。 这个引擎有很多责任，但是最重要的部分之一就是设计查询执行计划并以最佳和最有效的方式执行它。

I would like to mention here one of the most important factors of a SQL Server Execution Plan, which are SQL Server Statistics. Every time we issue a query, the SQL Server query-processing engine needs to estimate the data. To do so, SQL Server queries the Statistics to estimate the data. These estimates are critically important because based on the estimates; many important decisions are made about designing and finalizing SQL Server query execution plan. Therefore, keeping up to date statistics is critically important for a good query execution plan.

我想在此提及SQL Server执行计划中最重要的因素之一，即SQL Server统计信息。 每次我们发出查询时，SQL Server查询处理引擎都需要估计数据。 为此，SQL Server查询统计信息以估计数据。 这些估计至关重要，因为基于这些估计。 在设计和完成SQL Server查询执行计划时，需要做出许多重要的决定。 因此，对于一个好的查询执行计划而言，保持最新的统计数据至关重要。

SQL Server 2014 introduced a huge change in the query optimizer engine and the next change made in the query optimization engine is in SQL Server 2017. I am so excited to test new changes and so far, it looks very promising.

SQL Server 2014引入了查询优化器引擎的巨大更改，而查询优化引擎中的下一个更改是SQL Server2017。我很高兴能够测试新更改，到目前为止，它看起来非常有前途。

Before SQL Server 2017, the behavior of the SQL Server query-processing engine was to analyze the query first, create the plan and then execute it. Therefore, if the plan was somehow not appropriate, the query-processing engine was not able to change it while executing the query or even after it. At times, we see the query execution plans made by SQL Server are not appropriate. There are number of reasons behind poorly designed execution plan. Mentioned below are some of the basic reasons for bad execution plans:

在SQL Server 2017之前，SQL Server查询处理引擎的行为是先分析查询，创建计划然后执行它。 因此，如果该计划某种程度上不合适，则查询处理引擎将无法在执行查询时甚至在执行查询后对其进行更改。 有时，我们看到SQL Server制定的查询执行计划是不合适的。 设计不良的执行计划背后有许多原因。 下面提到的是执行计划不正确的一些基本原因：

• Lack of appropriate indexes
  缺乏适当的指标
• Outdated statistics
  过时的统计
• In-appropriate query execution plans cached with outdated values are stored
  存储使用过期值缓存的不合适查询执行计划
• Poorly written codes
  写得不好的代码

Therefore, there are two ways to fix these problems. One is that we let SQL Server fix the issue, by providing more relevant and accurate information about the query and secondly re-write the code so that it may perform in a better way.

因此，有两种方法可以解决这些问题。 一是通过提供有关查询的更相关和准确的信息，然后让SQL Server解决问题，其次是重新编写代码，以使其性能更好。

SQL Server 2017 introduced a new way of optimizing SQL Server Execution plan by introducing the “Adaptive Query Processing”. However, not much is out there about the feature, but I have tested some examples in SQL Server 2017 CTP 2, which we can download from the link here.

SQL Server 2017通过引入“ 自适应查询处理 ”引入了一种优化SQL Server执行计划的新方法。 但是，关于此功能的信息并不多，但是我已经在SQL Server 2017 CTP 2中测试了一些示例，我们可以从此处的链接下载这些示例。

The Adaptive Query Processing breaks the barrier between query plan optimization and actual execution. Now, we can have the optimization done while the actual query is executing or even after the actual query execution is completed to benefit the later executions.

自适应查询处理打破了查询计划优化和实际执行之间的障碍。 现在，我们可以在执行实际查询时甚至在完成实际查询执行之后进行优化，以使以后的执行受益。

Let me explain this a little bit more!

让我解释一下！

There are three parts of Adaptive Query Processing.

自适应查询处理分为三个部分。

1. Interleaved Executions
   交错执行
2. Batch Mode Memory Grant Feedback
   批处理模式内存授予反馈
3. Batch Mode Adaptive Joins
   批处理模式自适应联接

交错执行 (Interleaved Executions)

For Multi Statement Table Valued Functions prior to SQL Server 2017, estimate is set to a specific number, which is the nightmare for SQL Server DBAs. This can result in wrong selection of join operator, memory grants and resulting in poorly performing queries.

对于SQL Server 2017之前的多语句表值函数，估计值设置为特定数字，这是SQL Server DBA的噩梦。 这可能导致错误选择联接运算符，授予内存，并导致查询性能不佳。

The new feature can learn that even while the execution of the query, SQL Server Query Optimizer learns that if the estimates are way off than the actual ones, it adjusts the execution plan by actually executing a part of the query execution plan first and re-design the Query Execution Plan based on the actual amount of the rows. This leads to a much better plan, which is created and adjusted while the query is executing.

新功能可以得知，即使在执行查询的同时，SQL Server Query Optimizer也会知道，如果估算值与实际估算值相差甚远，它将通过首先实际执行查询执行计划的一部分然后重新执行来调整执行计划。根据行的实际数量设计查询执行计划。 这将导致更好的计划，该计划将在执行查询时创建和调整。

This feature comes right out of the box and you do not have to add any specific command to your queries to get the benefit from SQL Server 2017

此功能立即可用，您不必向查询中添加任何特定命令即可从SQL Server 2017中受益

For instance, if we create a simple Multi Statement Table Valued Function in SQL Server 2017 and execute it with Compatibility Level 130 (SQL Server 2016) and 140 (SQL Server 2017) then we can easily see the difference in the Estimated number of rows.

例如，如果我们在SQL Server 2017中创建一个简单的多语句表值函数，并以兼容级别130（SQL Server 2016）和140（SQL Server 2017）执行它，那么我们可以轻松地看到估计行数的差异。

SQL Server 2016中的查询执行计划和估计的行数 (Query Execution Plan and Estimated Number of Rows in SQL Server 2016)

In the screen below you can see the Estimated Number of Rows is 100 and Actual Number of rows are 10,000.

在下面的屏幕中，您可以看到“估计的行数”为100，“实际的行数”为10,000。

SQL Server 2017中的查询执行计划和估计的行数 (Query Execution Plan and Estimated Number of Rows in SQL Server 2017)

The same query executed with compatibility level 140 with no change in any structure or data and the result is an accurate estimation.

使用兼容性级别140执行的同一查询，不更改任何结构或数据，结果是准确的估计。

批处理模式内存授予反馈 (Batch Mode Memory Grant Feedback)

Memory Grant is another important process in creating an actual query execution plan. Prior to SQL Server 2017, the SQL Server Query Processing Engine used to cache a plan for stored procedures or queries and reuse them. For instance, if we have a Stored Procedure, which executed first time with a parameter resulting in a small amount of data, then the execution plan for that particular stored procedure would be cached for a small amount of data even for later larger data sets. This could result in a poor performance of the execution of the stored procedure with a parameter having a large amount of data. This could cause a small amount of memory grants for the query, but actually, it would have required more.

内存授予是创建实际查询执行计划的另一个重要过程。 在SQL Server 2017之前，SQL Server查询处理引擎用于缓存存储过程或查询的计划并重用它们。 例如，如果我们有一个存储过程，该存储过程用一个导致少量数据的参数首次执行，则该特定存储过程的执行计划将被缓存以存储少量数据，即使是后来的较大数据集也是如此。 这可能会导致带有大量数据的参数导致存储过程的执行性能下降。 这可能会导致查询获得少量的内存授权，但实际上，它会需要更多的内存。

In SQL Server 2017, the Batch Mode Memory Grant Feedback feature enables the SQL Server Query Processing engine to learn that if the memory grants are not sufficient then the engine will change the cached execution plan and update the memory grants so that the later executions should benefit with the new grants.

在SQL Server 2017中，批处理模式内存授予反馈功能使SQL Server查询处理引擎可以了解到，如果内存授予不足，则引擎将更改缓存的执行计划并更新内存授予，以便以后的执行应受益与新的赠款。

To show the Batch Mode Memory Grant in action I executed a query (Stored Procedure) which was having bad statistics and the memory grant was way off what was required.

为了显示批处理模式内存授予的实际运行情况，我执行了一个查询（存储过程），该查询的统计信息不正确，并且内存授予远非所需。

In SQL Server 2016’s Compatibility Mode the Query performed the same in all executions:

在SQL Server 2016的兼容模式下，该查询在所有执行中均执行相同的操作：

第一次执行 (First Execution)

This execution has Memory Grant 175984, which is not appropriate, as the estimated number of rows are 220384 an actual number of rows are Zero.

此执行具有内存授予175984 ，这不合适，因为估计的行数为220384 ，而实际的行数为零。

第二次执行 (Second Execution)

This execution again has the same Memory Grant and nothing was improved unless we change the statistics or the code itself.

该执行再次具有相同的“内存授予”，除非我们更改统计信息或代码本身，否则没有任何改善。

In SQL Server 2017’s Compatibility Mode, the Query performed bad in first execution, but automatically fixed the subsequent executions:

在SQL Server 2017的兼容模式下，查询在第一次执行时表现不佳，但会自动修复后续执行：

第一次执行 (First Execution)

This execution has Memory Grant 199872, which is not appropriate, as the estimated number of rows are 251091 an actual number of rows are Zero.

该执行具有Memory Grant 199872 ，这不合适，因为估计的行数为251091 ，而实际的行数为零。

第二次执行： (Second Execution:)

The Memory Grant has changed from 199872 to 19968 which is a huge difference and this has been done automatically without any code change or any server level configurations.

内存授权已从199872更改为19968，这是一个巨大的差异，并且无需任何代码更改或任何服务器级别配置即可自动完成。

批处理模式自适应联接 (Batch Mode Adaptive Joins)

Batch Mode Adaptive Joins are also a great way to improve the query performance based of the number of rows flowing through the actual execution plan. The concept here is simple; the execution engine defers the choice of a Hash Join or a Nested Loop Join until the first join in the query execution plan is made. After that first join has been executed and based on the number of records fetched, the SQL Server Query Processing engine decides whether to choose Hash Join or Nested Loop Join.

基于流经实际执行计划的行数，批处理模式自适应联接也是一种提高查询性能的好方法。 这里的概念很简单； 执行引擎将选择“哈希联接”或“嵌套循环联接”推迟到查询执行计划中的第一个联接完成为止。 在执行了第一次联接并根据获取的记录数之后，SQL Server查询处理引擎将决定是选择哈希联接还是嵌套循环联接。

As of now, I only have only one point regarding this as the SQL Server Program Managers disclosed nothing else. This is the number of rows threshold. Generally, if the number of rows is small then Nested Loop Join is used, otherwise with large data set the Hash Join will be selected to enhance the query performance.

到目前为止，由于SQL Server程序管理器没有透露其他任何内容，因此我对此仅持保留意见。 这是行数阈值。 通常，如果行数少，则使用嵌套循环联接，否则，如果数据集较大，则将选择哈希联接以提高查询性能。

For the Batch Adaptive Joins I am not able to try it, as it requires some Trace Flags to be enabled, which are still not public.

对于批量自适应联接，我无法尝试，因为它需要启用某些跟踪标志，但这些跟踪标志仍然不公开。

摘要 (Summary)

SQL Server 2017 is really a big change in SQL Server and this is just the start. There are number of other features, which will make the life of Developers and Database Administrator a lot easier. Even with these enhancements in the SQL Server 2017 Query Processing engine, the results are faster queries and optimized resource consumption. I am still waiting for further announcements to be made for SQL Server 2017.

SQL Server 2017确实是SQL Server的重大变化，这仅仅是开始。 还有许多其他功能，这些功能将使开发人员和数据库管理员的工作更加轻松。 即使在SQL Server 2017查询处理引擎中进行了这些增强，结果仍然是更快的查询和优化的资源消耗。 我仍在等待SQL Server 2017的进一步公告。

翻译自: https://www.sqlshack.com/adaptive-query-processing-in-sql-server-2017/