sql-server - 为什么我的两台服务器之间的实际执行计划不同？

Question

我有一个在生产和开发环境中运行的 SQL Server 查询。完全相同的查询。

SELECT DISTINCT 
    [Record_Transformation_ACCRUALS],
    [Record_Transformation_FA:AMORTIZATION],   
    [Record_Transformation_BONUS:AMORTIZATION],
    [Record_Transformation_CPH:BYLABOUR],
    [Record_Transformation_CPH:BYTARGETHOURS],
    [Record_Transformation_OVERHEAD:CULTURE],
    [Record_Transformation_DEDICATED COSTCENTER],
    [Record_Transformation_PUSHDOWN:EXPENSE],
    [Record_Transformation_OVERHEAD:FACILITIES],
    [Record_Transformation_OVERHEAD:GENOME],
    [Record_Transformation_TAXES:MANAGEMENT],
    [Record_Transformation_TAXES:MARKETING],
    [Record_Transformation_OVERHEAD:OFFICETECH],
    [Record_Transformation_EXPENSE:PASSTHROUGH],
    [Record_Transformation_OVERHEAD:PEOPLEPRACTICES],
    [Record_Transformation_OVERHEAD:RECRUITING],
    [Record_Transformation_TAXES:SALES],
    [Record_Transformation_Static Transfer],
    [Record_Label] 
FROM
    Warehouse_20181204 
WHERE 
    Is_Target_Employee = 1 OR Is_Source_Employee = 1

我们比较了这两个表的创建脚本，它们是相同的(除了相关表的名称之外)。

我们还验证了它们都使用聚集列存储索引。

在开发过程中，此查询只需要不到一秒的时间。在产品上大约需要一分钟。我们一开始认为数据的大小可能是问题所在，但差异很小(几十万行)。

然后我们检查了两者的实际执行计划。在开发中实际的执行计划是:

在产品上，实际的执行计划非常不同:

我们发现自己很难理解为什么会这样。我们已验证 SQL Server 的版本是相同的:

Microsoft SQL Server 2017 (RTM-CU5) (KB4092643) - 14.0.3023.8 (X64)   
Web Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393:) (Hypervisor)

我的问题有两个:

1. 我们如何确定生产和开发之间的执行计划为何如此不同？
2. 在给定类似数据集的情况下，我们如何才能让 prod 环境与 dev 环境一样快地运行？

编辑:

一些额外要求的详细信息:

• 两台服务器都有 8G 内存，运行时都有超过 1G 的可用内存
• 两台服务器都有 2 个处理器
• 硬件与您所获得的一样 - 两个 AWS 实例大小相同
• 我们已验证两个表和聚集列存储索引的 sql 是否相同

希望这些对当前 SQL 计划的所有其他细节有所帮助:

DEV sql 计划:https://gist.github.com/klick-barakgall/17a7ce926777a3257f7eecb32859458e

生产 SQL 计划:https://gist.github.com/klick-barakgall/76eabf1008f5bfb0c51259c2ba3f509d

为那些有兴趣深入研究执行情况的人添加粘贴计划的链接。

DEV

PROD

Answer 1

串行与并行并不是真正的问题，因为无论如何，您的最大并行度只有 2。

导致查询缓慢的原因是内存授予不足和大量排序溢出(达到第 8 级)

您的查询返回 305 行，但 SQL Server 在一个计划中估计为 2,561,980 行，在另一个计划中估计为 3,709,060 行。

对于 305 行，您需要一个哈希聚合，因为它只需要存储 305 个不同分组值的内存，而不需要存储整个 600 万行的内存以及排序使用的额外开销。

即使在具有哈希聚合的计划中，输出行数的过高估计也意味着您会收到过多的内存授予警告。

The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 831,800 KB, Final 831,800 KB, Used 20,480 KB.

为此，您可以尝试以下方法来创建多列统计信息，目的是准确估计组的数量，以便 SQL Server 自然地选择具有适当大小的内存授予的哈希组。可能不需要 FULLSCAN，但是当我设置测试时，默认采样似乎不足以让优化器使用来自新统计数据和新基数估计器的密度信息。

CREATE STATISTICS SomeName ON  Warehouse_20181204  (
    [Record_Transformation_ACCRUALS],
    [Record_Transformation_FA:AMORTIZATION],   
    [Record_Transformation_BONUS:AMORTIZATION],
    [Record_Transformation_CPH:BYLABOUR],
    [Record_Transformation_CPH:BYTARGETHOURS],
    [Record_Transformation_OVERHEAD:CULTURE],
    [Record_Transformation_DEDICATED COSTCENTER],
    [Record_Transformation_PUSHDOWN:EXPENSE],
    [Record_Transformation_OVERHEAD:FACILITIES],
    [Record_Transformation_OVERHEAD:GENOME],
    [Record_Transformation_TAXES:MANAGEMENT],
    [Record_Transformation_TAXES:MARKETING],
    [Record_Transformation_OVERHEAD:OFFICETECH],
    [Record_Transformation_EXPENSE:PASSTHROUGH],
    [Record_Transformation_OVERHEAD:PEOPLEPRACTICES],
    [Record_Transformation_OVERHEAD:RECRUITING],
    [Record_Transformation_TAXES:SALES],
    [Record_Transformation_Static Transfer],
    [Record_Label] ) WITH FULLSCAN

或者您可以考虑重组数据库，以便 20 左右列位于包含 305 行的单独表中(加上数据中存在但被 WHERE 子句排除的任何其他组合)，并且原始表只包含一个 id 引用回这个新表。

然后您可以将其重写为 SELECT from thesmall table where groupid IN (SELECT groupid FROM large table WHERE....)