主页 / dba / 问题 / 53726
Accepted
Kin Shah
Asked: 2013-11-22 09:18:08 +0800 CST

UAT 和 PROD 服务器上执行计划的差异

  • 772

我想了解为什么在 UAT（3 秒内运行）与 PROD（23 秒内运行）上执行相同的查询会有如此巨大的差异。

UAT 和 PROD 都有准确的数据和索引。

询问：

set statistics io on;
set statistics time on;

SELECT CONF_NO,
       'DE',
       'Duplicate Email Address ''' + RTRIM(EMAIL_ADDRESS) + ''' in Maintenance',
       CONF_TARGET_NO
FROM   CONF_TARGET ct
WHERE  CONF_NO = 161
       AND LEFT(INTERNET_USER_ID, 6) != 'ICONF-'
       AND ( ( REGISTRATION_TYPE = 'I'
               AND (SELECT COUNT(1)
                    FROM   PORTFOLIO
                    WHERE  EMAIL_ADDRESS = ct.EMAIL_ADDRESS
                           AND DEACTIVATED_YN = 'N') > 1 )
              OR ( REGISTRATION_TYPE = 'K'
                   AND (SELECT COUNT(1)
                        FROM   CAPITAL_MARKET
                        WHERE  EMAIL_ADDRESS = ct.EMAIL_ADDRESS
                               AND DEACTIVATED_YN = 'N') > 1 ) )

在 UAT 上：

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 11 ms, elapsed time = 11 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(3 row(s) affected)
Table 'Worktable'. Scan count 256, logical reads 1304616, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PORTFOLIO'. Scan count 1, logical reads 84761, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2418 ms,  elapsed time = 2442 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

在此处输入图像描述

在产品上：

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(3 row(s) affected)
Table 'PORTFOLIO'. Scan count 256, logical reads 21698816, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 23937 ms,  elapsed time = 23935 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

在此处输入图像描述

请注意，在 PROD 上，查询建议缺少索引，正如我测试过的那样，这是有益的，但这不是讨论的重点。

我只想了解：在 UAT 上 - 为什么 sql server 创建一个工作表而在 PROD 上却没有？它在 UAT 而不是 PROD 上创建表假脱机。另外，为什么 UAT 与 PROD 的执行时间如此不同？

笔记 ：

我在两台服务器上都运行 sql server 2008 R2 RTM（很快就会用最新的 SP 打补丁）。

UAT：最大内存 8GB。MaxDop、处理器亲和性和最大工作线程数为 0。

Logical to Physical Processor Map:
*-------  Physical Processor 0
-*------  Physical Processor 1
--*-----  Physical Processor 2
---*----  Physical Processor 3
----*---  Physical Processor 4
-----*--  Physical Processor 5
------*-  Physical Processor 6
-------*  Physical Processor 7

Logical Processor to Socket Map:
****----  Socket 0
----****  Socket 1

Logical Processor to NUMA Node Map:
********  NUMA Node 0

产品：最大内存 60GB。MaxDop、处理器亲和性和最大工作线程数为 0。

Logical to Physical Processor Map:
**--------------  Physical Processor 0 (Hyperthreaded)
--**------------  Physical Processor 1 (Hyperthreaded)
----**----------  Physical Processor 2 (Hyperthreaded)
------**--------  Physical Processor 3 (Hyperthreaded)
--------**------  Physical Processor 4 (Hyperthreaded)
----------**----  Physical Processor 5 (Hyperthreaded)
------------**--  Physical Processor 6 (Hyperthreaded)
--------------**  Physical Processor 7 (Hyperthreaded)

Logical Processor to Socket Map:
********--------  Socket 0
--------********  Socket 1

Logical Processor to NUMA Node Map:
********--------  NUMA Node 0
--------********  NUMA Node 1

更新 ：

UAT 执行计划 XML：

http://pastebin.com/z0PWvw8m

产品执行计划 XML：

http://pastebin.com/GWTY16YY

UAT 执行计划 XML - 从 PROD 生成的计划：

http://pastebin.com/74u3Ntr0

服务器配置：

产品：PowerEdge R720xd - Intel(R) Xeon(R) CPU E5-2637 v2 @ 3.50GHz。

UAT：PowerEdge 2950 - Intel(R) Xeon(R) CPU X5460 @ 3.16GHz

我已在answers.sqlperformance.com上发布

更新 ：

感谢@swasheck 的建议

将 PROD 的最大内存从 60GB 更改为 7680 MB，我可以在 PROD 中生成相同的计划。查询与 UAT 同时完成。

现在我需要了解 - 为什么？另外，这样一来，我就无法证明这个怪物服务器取代旧服务器是合理的！

performance sql-server-2008-r2

3 个回答

  1. Best Answer

    缓冲池的潜在大小以多种方式影响查询优化器的计划选择。据我所知，超线程不会影响计划选择（尽管潜在可用调度程序的数量当然可以）。

    工作区内存

    对于包含诸如排序和散列之类的内存消耗迭代器的计划，缓冲池的大小（除其他外）决定了运行时查询可能可用的最大内存授予量。

    在 SQL Server 2012（所有版本）中，此数字在查询计划的根节点上报告，在Optimizer Hardware Dependencies部分中显示为Estimated Available Memory Grant。2012 年之前的版本不会在展示计划中报告此数字。

    估计的可用内存授权是查询优化器使用的成本模型的输入。因此，与设置较低的机器相比，在缓冲池设置较大的机器上更可能选择需要较大排序或散列操作的计划替代方案。对于具有大量内存的安装，这种想法的成本模型可能会走得太远 - 选择具有非常大的排序或散列的计划，其中替代策略更可取（KB2413549 - 使用大量内存可能会导致SQL Server 中的低效计划 - TF2335）。

    在您的情况下，工作空间内存授予不是一个因素，但值得了解。

    数据访问

    缓冲池的潜在大小也会影响优化器的数据访问成本模型。模型中的一个假设是每个查询都以冷缓存开始 - 因此假设第一次访问页面会产生物理 I/O。该模型确实试图考虑重复访问来自缓存的可能性，这个因素取决于缓冲池的潜在大小等。

    问题中显示的查询计划中的 Clustered Index Scans 是重复访问的一个示例；对于嵌套循环半连接的每次迭代，扫描都被倒带（重复，不改变相关参数）。半连接的外部输入估计有 28.7874 行，这些扫描的查询计划属性显示估计的回绕结果为 27.7874。

    同样，仅在 SQL Server 2012 中，计划的根迭代器显示该Estimated Pages Cached部分中的数量Optimizer Hardware Dependencies。该数字报告了成本计算算法的输入之一，该算法看起来考虑了来自缓存的重复页面访问的机会。

    其效果是，与具有较小最大缓冲池大小的安装相比，具有更高配置的最大缓冲池大小的安装将倾向于降低多次读取相同页面的扫描（或查找）成本。

    在简单的计划中，通过与估计的操作员成本进行比较，可以看出倒带扫描的成本降低(estimated number of executions) * (estimated CPU + estimated I/O)，后者会更低。由于半联接和联合的影响，示例计划中的计算更加复杂。

    然而，问题中的计划似乎显示了重复扫描和创建临时索引之间的选择非常平衡的情况。在具有较大缓冲池的机器上，重复扫描的成本略低于创建索引。在缓冲池较小的机器上，扫描成本降低的幅度较小，这意味着索引假脱机计划对优化器来说看起来稍微便宜一些。

    计划选择

    优化器的成本模型做了很多假设，并且包含了大量的详细计算。并非总是（甚至通常）可以跟踪所有细节，因为并非我们需要的所有数字都被公开，并且算法可以在版本之间更改。特别是，用于考虑遇到缓存页面的机会的缩放公式并不为人所知。

    在这种特殊情况下更重要的是，优化器的计划选择无论如何都是基于不正确的数字。Clustered Index Seek 的估计行数为 28.7874，而在运行时遇到 256 行 - 几乎是一个数量级。我们无法直接看到优化器所拥有的关于这 28.7874 行中值的预期分布的信息，但它也很可能是非常错误的。

    当估计出现这种错误时，计划选择和运行时性能基本上并不比机会好。带有索引假脱机的计划恰好比重复扫描执行得更好，但是认为增加缓冲池的大小是导致异常的原因是完全错误的。

    在优化器拥有正确信息的情况下，它产生一个体面的执行计划的机会要大得多。具有更多内存的实例在工作负载上的性能通常比具有更少内存的另一个实例更好，但不能保证，尤其是当计划选择基于不正确的数据时。

    这两个实例都以自己的方式提出了缺失的索引。一个报告了明确的缺失索引，另一个使用了具有相同特征的索引线轴。如果索引提供良好的性能和计划稳定性，那可能就足够了。我也倾向于重写查询，但这可能是另一回事。

    • 44

  2. Paul White以非常清晰的方式解释了背后的原因 - 在具有更多内存的服务器上运行时的 sql 服务器行为。

    另外，非常感谢@swasheck第一次发现这个问题。

    用微软打开了一个案例，下面是建议的。

    该问题通过使用跟踪标志 T2335 作为启动参数得到解决。

    KB2413549 - 使用大量内存会导致 SQL Server 中的计划效率低下，对此进行了更详细的描述。

    此跟踪标志将导致 SQL Server 在执行查询时生成在内存消耗方面更加保守的计划。它不限制 SQL Server 可以使用的内存量。为 SQL Server 配置的内存仍将被数据缓存、查询执行和其他消费者使用。请确保在将其投入生产环境之前彻底测试此选项。

    • 19

  3. 最大内存设置和超线程都会影响计划选择。

    此外，我注意到您的“设置”选项在每个环境中都不同：

    UAT 上的 StatementSetOptions：

    ANSI_NULLS="true" 
ANSI_PADDING="true" 
ANSI_WARNINGS="true" 
ARITHABORT="true" 
CONCAT_NULL_YIELDS_NULL="true" 
NUMERIC_ROUNDABORT="false" 
QUOTED_IDENTIFIER="true"

    产品上的 StatementSetOptions：

    ANSI_NULLS="true" 
ANSI_PADDING="true" 
ANSI_WARNINGS="true" 
ARITHABORT="false" 
CONCAT_NULL_YIELDS_NULL="true"
NUMERIC_ROUNDABORT="false"
QUOTED_IDENTIFIER="true"

    SQL 可以根据 SET 选项生成不同的计划。如果您从不同的 SSMS 会话或从应用程序的不同执行中捕获计划，则经常会发生这种情况。

    确保开发人员使用一致的连接字符串。

    • 13

相关问题