快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

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

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

随着SQL Server新版本的发布,查询优化器已经进行了许多改进。2017年新增的自适应查询处理代表了一个新的方向。这组功能为各种联接类型,多语句表值函数和内存授予提供了一定的灵活性。在本文中,Robert Sheldon解释了构成自适应查询处理的三个功能。

SQL Server 2017现在提供自适应查询处理,这是一组旨在提高查询性能的新功能。自适应查询处理解决了与执行计划中基数估计有关的问题,从而为多语句表值函数(MSTVF)提供了更好的内存分配,联接类型选择和行计算。

在SQL Server 2017之前,如果查询计划包含不正确的基数估计,则数据库引擎将继续对每个语句执行使用该计划,只要该计划仍被缓存,通常会导致查询性能不佳。例如,执行计划可能会为某些查询分配过多的内存,而低估了其他查询的内存需求。

自适应查询处理功能试图通过在计算查询执行计划时提供更准确的基数估计来解决这些类型的问题。默认情况下,SQL Server 2017在兼容级别为140或更高的数据库上启用这些功能。

如果数据库的兼容性级别较低,则可以使用一条ALTER DATABASE语句来更改该级别。例如,以下语句将WideWorldImporters示例数据库的兼容性级别更改为140

WideWorldImporters数据库用于本文中的所有示例。如果您的系统上安装了此数据库,则应该可以尝试这些示例而无需进行任何更改。如果要使用其他数据库,则可以创建SELECT与示例中显示的语句相当的语句。相同的原则应适用于SQL Server 2017上运行的兼容级别为140的任何数据库。

您可以通过运行以下SELECT语句,在WHERE子句中输入数据库名称来验证数据库的兼容性级别:

如果SELECT在执行前一条语句后运行此语句ALTER DATABASE,则该SELECT语句应返回的值140

在数据库上设置兼容性级别是您要在SQL Server 2017中启用该数据库的自适应查询处理功能所需的唯一步骤。当前,SQL Server 2017支持三种自适应查询处理类型:

  • 批处理模式内存授予反馈
  • 批处理模式自适应联接
  • 交错执行

如前所述,这些功能默认情况下处于启用状态。但是,您可以单独禁用或启用每个数据库,而无需更改数据库的兼容性级别。以下各节更详细地介绍了这三个功能,包括禁用或启用它们的必要步骤。

内存授予反馈

SQL Server在联接和排序操作期间使用内存来存储行数据。编制执行计划时,查询引擎会估计存储这些行需要多少内存。如果内存估计值太小,多余的数据将溢出到磁盘上,从而影响性能。如果估算值太大,则会浪费内存,从而影响并发操作的性能。

内存授予反馈功能通过在第一次执行该语句时重新计算行内存要求来帮助纠正这种情况。如果初始估计值不正确,则更新缓存的计划。只要查询计划保留在缓存中,随后的执行就可以从新的估计中受益。

理解内存授予反馈如何工作的最佳方法是查看它的运行情况,从SQL Server传统上在估算内存需求时的行为开始。若要演示此行为,首先通过运行以下ALTER DATABASE语句来禁用内存授予反馈功能:

该语句将DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK配置选项设置为ON,这将禁用内存授予反馈功能,而不会影响数据库的兼容性级别。要验证设置是否已更新,请运行以下SELECT语句:

SELECT语句返回有关数据库的范围配置设置的数据,如图1所示。

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

图1.禁用内存授权反馈

结果的第6行包含该DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK选项。请注意,该选项的当前值为1ON),默认值为0OFF),表示默认情况下启用了内存授予反馈(但仅对于兼容性级别为140或更高的数据库)。

接下来,在启用SELECT“ 实际执行计划”的情况下运行以下语句:

语句运行后,转到执行计划,并将鼠标悬停在Select运算符上以显示该运算符的详细信息,如图2所示。

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

图2. Select运算符的Memory Grant属性

“ 内存授予”属性指示查询的行数据需要78,464 KB的内存。无论您重新运行该SELECT语句多少次,只要查询计划仍处于缓存状态,您都应该获得相同的“ 内存授予”总计。即使您收到的总数与此处显示的总数不同,其行为也应该相同。

考虑到这一点,您现在可以通过重新启用该功能,然后重新执行该SELECT语句来测试内存授予反馈功能。要重新启用该功能,请运行以下ALTER DATABASE语句,将该DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK选项设置为OFF

当您将DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK选项转到时OFF,该选项将不再在sys.database_scoped_configurations表中列出。仅当您将选项设置为时,ON它才包含在表中。对于特定于启用或禁用自适应查询处理功能的所有作用域配置选项,都是如此。

重新启用内存授予反馈功能后,应重新运行示例SELECT语句。但是,在执行此操作之前,请从缓存中清除执行计划。(如果合适,您应该清除每个示例之间的缓存,以确保在测试这些语句时看到正确的行为。但是请不要在生产服务器上执行此操作。实际上,您永远不应在生产服务器上测试新功能。 。)清除缓存的一种方法是运行以下T-SQL语句:

SQL Server提供了几种清除缓存的方法,因此请选择一种适合您的方法。该DBCC FREEPROCCACHE语句是一种非常简单的方法,只要可以从缓存中清除所有查询计划即可。如果不是,则必须指定要删除的特定计划。

重新启用内存授予反馈功能并清除缓存后,SELECT两次或多次运行以下语句(SELECT与上面的语句相同):

第一次重新运行此语句时,您应该收到与以前相同的结果,“ 内存授予”属性显示的内存总计为78,464 KB,或接近该值。但是,当您重新运行该语句时,总数应该低得多。在我的系统上,后续执行产生了总计14,592 KB 的Memory Grant,如图3所示。

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

图3. Select运算符的Memory Grant属性

当我在系统上测试内存授予反馈功能时,我SELECT多次重复执行上述声明。尽管我通常会收到与此处所述相同的“ 内存授权”总数,但在某些情况下,我会获得原始估计甚至其他值。然而,尽管不一致的情况相对较少,但在大多数情况下,该功能仍按广告宣传进行工作。

您还可以通过包含一个OPTION指定hint 的子句来基于每个语句禁用内存授予反馈功能DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK,如以下SELECT语句所示:

不管您多久重新运行一次语句,“ 内存授予”属性应再次显示总共78,464 KB的内存(或类似的内存),至少直到重新缓存该计划为止。

自适应联接

SELECT语句包含连接条件时,查询引擎将尝试基于估计的行数来确定要使用的最佳连接类型。在SQL Server 2017之前,如果执行计划选择了错误的联接类型,除了指定查询提示或特定的联接类型之外,几乎无能为力。

如有必要,新的自适应联接功能可通过在语句执行期间选择其他联接类型来帮助纠正这种情况。在扫描完第一个输入后,执行计划将根据计算出的阈值确定是将联接类型更改为哈希联接还是嵌套循环联接。

通过将旧方法与新方法进行比较,可以看到此功能的工作原理,类似于测试内存授予反馈功能时所采用的方法。要禁用自适应联接功能,请运行以下ALTER DATABASE语句,并将DISABLE_BATCH_MODE_ADAPTIVE_JOINS选项设置为ON

毫不奇怪,该DISABLE_BATCH_MODE_ADAPTIVE_JOINS选项特定于自适应联接。但是,自适应查询处理选项的工作原理相同。要禁用功能,请将其相关选项设置为ON,要启用该功能,请将选项设置为OFF

要验证该选项已设置为ON并且功能已禁用,可以运行以下SELECT语句:

SELECT语句返回如图3所示的结果,表明该DISABLE_BATCH_MODE_ADAPTIVE_JOINS选项已设置为1ON),默认值为0OFF)。

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

图4.禁用自适应联接

接下来,运行以下SELECT语句,仅这次启用实时查询统计信息

执行计划应类似于图5所示,其中显示了列存储索引扫描,非聚集索引扫描和哈希联接。

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

图5.执行非自适应联接

由于启用了“ 实时查询统计”,因此该计划还显示了行数与估计行数的计数,所有这些看起来都非常简单。实际上,我包含此示例只是为了在启用自适应联接时将其与查询计划进行比较。

然后,下一步是通过运行以下ALTER DATABASE语句来启用自适应联接:

运行此语句后,请重新运行前面的SELECT语句(为方便起见,再次在此处显示):

现在看一下执行计划。您会发现几个附加项,包括“ 聚簇索引查找”运算符,更重要的是,新的“ 自适应联接”运算符,如图6所示。

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

图6.执行自适应联接

如果需要的话,可以包含“ 聚集索引查找”运算符,以供嵌套循环连接使用。请注意,指定了24370中的0,表示该分支未使用,这意味着为此操作选择了哈希联接。

所述自适应加入操作者确定什么加入类型用于通过计算确定是否执行哈希联接或嵌套循环连接,基于该行数的阈值。在这种情况下,该阈值为159.754,行数为24,459。如果行计数大于或等于阈值,则查询计划将使用哈希联接。否则,该计划将使用嵌套循环联接。

如果将鼠标悬停在“ 自适应 联接”运算符上以显示详细信息,您将看到它们包括三个重要属性:

  • 估计联接类型,设置为HashMatch
  • 自适应阈值行,设置为159.754
  • 是自适应的,设置为True

图7显示了在启用了自适应连接功能的情况下运行该语句之后,自适应连接运算符的详细信息SELECT

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

图7. 自适应联接运算符的属性

假设您现在UPDATE对该InvoiceLines表运行以下语句:

接下来,SELECT再次运行前面的语句,只有这次在子句中指定一个Quantity值:360WHERE

这次,“ 自适应 联接”运算符的详细信息将联接类型显示为NestedLoops,阈值显示为104.24。

如果要将WorldWideImporters数据库恢复到其原始状态,请运行以下UPDATE语句:

请注意,自适应联接功能会带来额外的内存开销,并且当前仅支持SELECT语句(不支持数据修改语句)。此外,该语句必须同时适用于哈希联接和嵌套循环联接,才能使用自适应联接功能。

与内存授予反馈功能类似,您可以通过包含一个OPTION子句并指定DISABLE_BATCH_MODE_ ADAPTIVE_JOINS提示来在每个语句的基础上禁用自适应联接,如以下SELECT语句所示:

当包含该OPTION子句时,该SELECT语句将像在兼容级别早于140的数据库中一样运行,但不会影响当前的兼容级别。

交错执行

在SQL Server 2017之前的版本中,当一条语句包含MSTVF时,无论该函数实际返回多少行,执行计划都会将行估计固定为100。对于小型数据集,这通常不是问题,但是当估计值与实际计数之间存在较大差异时,性能可能会受到影响。

交错执行功能可以通过暂停执行足够长的时间以捕获更准确的基数,然后将该信息用于下游操作来帮助解决此问题。但是,应该注意的是,如果使用MSTVF包含复杂的逻辑并且将针对大量行进行联接,则仍会导致性能问题。

若要查看此功能的工作原理,请首先运行以下CREATE FUNCTION语句,该语句定义了一个非常简单的MSTVF:

接下来,通过运行以下ALTER DATABASE语句,将DISABLE_INTERLEAVED_EXECUTION_TVF选项设置为,以禁用交错执行功能ON

就像您在前面的示例中看到的一样,只是它特定于交错执行。与以前一样,要验证是否已将选项设置为ON并且功能已禁用,可以运行以下SELECT语句:

SELECT语句返回图8所示的结果,表明该DISABLE_INTERLEAVED_EXECUTION_TVF选项已设置为1ON),默认值为0OFF)。

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

图8.禁用交错执行

接下来,在启用SELECT“ 实际执行计划”的情况下运行以下语句:

SELECT语句将GetInvoiceLines函数连接到Sales.Invoices表,100作为函数的参数值传入。接下来,转到执行计划,并将鼠标悬停在表值函数运算符上。操作员详细信息的“ 估计行数”属性应显示为100,如图9所示。

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

图9. 表值函数运算符的估计行数属性

尽管“ 表值函数”运算符估计有100行,但该函数实际返回24,459行,这是两个数量之间的重大差异。您可以通过查看“ 表扫描”运算符的详细信息(“ 行数”属性)或直接查询该函数来查看此金额。

要查看交错执行如何更改此行为,请首先通过将DISABLE_INTERLEAVED_EXECUTION_TVF选项设置为来重新启用该功能OFF

接下来,SELECT从上方重新运行该语句,并在100调用函数时传递相同的参数值():

最后,转到执行计划并将鼠标悬停在表值函数运算符上。现在,操作员详细信息的“ 估计行数”属性应该显示值为24459,如图10所示。

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

图10.表值函数运算符的估计行数属性

能够返回MSTVF的更准确的行估计值可以帮助提高查询性能,尤其是当函数返回大量行时。但是,在某些情况下,您可能希望基于每个语句禁用此功能,就像在其他自适应查询处理功能中看到的那样:

当包含此OPTION子句并指定提示时DISABLE_INTERLEAVED_EXECUTION_TVF表值函数运算符将再次显示100行的估计值。

自适应查询处理

根据您正在运行的查询的类型,自适应查询处理功能可以显着提高查询性能,尤其是随着工作量的增长。目前尚不清楚微软是否会在短期内增强这些功能,但是似乎我们会看到一些改进。例如,Microsoft最终可能会将自适应联接功能扩展到数据修改语句,或者将交错执行功能扩展到MSTVF之外。实际上,Microsoft已经在Azure SQL数据库和SQL Server 2019中发布了新的表变量延迟编译功能的公开预览。

如果您要使用SQL Server 2017,则应考虑更新那些可能会从自适应查询处理中受益的数据库的兼容性级别。只需确保完全测试数据库以确保您没有引入任何新问题即可。如果不确定组织是否将迁移到SQL Server 2017,则可以在测试其他新功能时尝试使用自适应查询处理功能,以帮助您确定是否值得进行升级。

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638