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

我想了解为什么对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.

在PROD上：

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

PROD：最大内存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

UPDATE：

UAT执行计划XML：

http://pastebin.com/z0PWvw8m

PROD执行计划XML：

http://pastebin.com/GWTY16YY

UAT执行计划XML-通过PROD生成计划：

http://pastebin.com/74u3Ntr0

服务器配置：

PROD ：PowerEdge R720xd-Intel®Xeon®CPU E5-2637 v2 @ 3.50GHz。

UAT：PowerEdge 2950-Intel（R）Xeon（R）CPU X5460 @ 3.16GHz

我已经在answer.sqlperformance.com上发布了

>
更新：

感谢@swasheck的建议
将PROD的最大内存从60GB更改为7680 MB，我能够在其中生成相同的计划PROD。该查询与UAT同时完成。

现在我需要了解-为什么？而且，由此，我将无法证明这个怪物服务器取代了旧服务器！

#1 楼

缓冲池的潜在大小会以多种方式影响查询优化器的计划选择。据我所知，超线程不会影响计划的选择（尽管潜在可用调度程序的数量当然可以）。
工作区内存
对于包含诸如排序和哈希之类的消耗内存的迭代器的计划，缓冲池的大小（除其他事项外）确定了运行时查询可使用的最大内存授予量。
在SQL Server 2012（所有版本）中，此数字在查询的根节点上报告计划，在Optimizer Hardware Dependencies部分中，显示为Estimated Available Memory Grant。 2012年之前的版本未在显示计划中报告此数字。
估计的可用内存授权是查询优化器使用的成本模型的输入。结果，与具有较低设置的计算机相比，在具有较大缓冲池设置的计算机上更有可能选择需要较大排序或哈希运算的计划替代方案。对于具有大量内存的安装，采用这种思路可能会导致成本模型走得太远-选择具有较大种类或哈希值的方案，在这种方案中最好采用替代策略（KB2413549-使用大量内存可能会导致SQL Server中的效率低下的计划-TF2335）。
在您的情况下，工作空间内存授予不是一个因素，但值得了解。
数据访问
缓冲池的潜在大小也影响优化器的数据访问成本模型。该模型中的一个假设是，每个查询均以冷缓存开始-因此，假定对页面的首次访问会导致物理I / O。该模型确实尝试考虑了重复访问来自缓存的机会，这取决于缓冲池的潜在大小等。
问题中显示的查询计划中的聚簇索引扫描是重复访问的一个示例。对于嵌套循环半连接的每次迭代，重新扫描（重复，不更改相关参数）。半联接的外部输入估计28.7874行，并且这些扫描的查询计划属性显示结果估计为27.7874倒带。
同样，仅在SQL Server 2012中，计划的根迭代器显示行数。在Estimated Pages Cached部分中的Optimizer Hardware Dependencies。该数字报告了成本核算算法的输入之一，该成本核算是考虑到来自缓存的重复页面访问的机会。
结果是，具有更高配置的最大缓冲池大小的安装将倾向于降低成本读取（或搜索）多次读取相同页面的次数（而不是最大缓冲池较小的安装）一次。
在简单的计划中，通过将(estimated number of executions) * (estimated CPU + estimated I/O)与估算值进行比较，可以看到回卷扫描的成本降低运营商成本，这将更低。由于半连接和联合的影响，示例计划中的计算更加复杂。
但是，问题中的计划似乎显示出在重复扫描和创建临时索引之间进行选择的情况很好地平衡。在具有更大缓冲池的计算机上，重复扫描的成本比创建索引要低一些。在缓冲池较小的计算机上，扫描成本减少了较小的数量，这意味着索引假脱机计划对于优化器而言似乎稍微便宜一些。
计划选择
优化器的成本模型有许多假设，并包含大量详细的计算。并非总是（甚至通常）可能会遵循所有细节，因为并非公开了我们所需的所有数字，并且算法可能会在发行版之间进行更改。特别是，考虑到遇到缓存页面的机会所应用的缩放公式并不为人所知。
在这种情况下，最重要的是，优化器的计划选择始终基于不正确的数字。从聚簇索引查找中估计的行数为28.7874，而在运行时遇到256行-几乎超出了一个数量级。我们无法直接看到优化器具有的有关那些28.7874行中的值的预期分布的信息，但也很可能也是错误的。
当估计错误时，计划选择和运行时性能就基本上没有胜于机会。具有索引假脱机的计划碰巧比重复扫描执行得更好，但是认为增加缓冲池的大小是造成异常的原因是完全错误的。
如果优化程序具有正确的信息，则机会好得多，它将产生一个体面的执行计划。具有更多内存的实例通常会比具有较少内存的另一个实例在工作负载上表现更好，但是并不能保证，尤其是当计划选择基于错误的数据时。
两个实例都建议以自己的方式缺少索引。一个报告了显式丢失的索引，另一个报告了具有相同特征的索引假脱机。如果索引提供了良好的性能和计划稳定性，那可能就足够了。我的倾向是也重写查询，但这可能是另一回事了。

#2 楼

Paul White非常清楚地解释了背后的原因-在具有更多内存的服务器上运行时sql服务器行为。

非常感谢@swasheck首次发现问题。

建议与Microsoft一起开案件，并提出以下建议。

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

KB2413549-使用大量内存可能会导致SQL Server的计划效率低下，因此请对其进行详细描述。

此跟踪标志将导致SQL Server生成在内存方面更为保守的计划执行查询时的消耗。它不限制SQL Server可以使用多少内存。为SQL Server配置的内存仍将由数据缓存，查询执行和其他使用者使用。在将其应用到生产环境中之前，请确保您彻底测试了此选项。

#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会话或从应用程序的不同执行中捕获计划，则经常会发生这种情况。

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

您正确地说最大内存和超线程可能会影响计划缓存，但是我想详细了解发生这种情况的原因和原因。感谢您的回答。

–金沙（Kin Shah）
13年11月25日在20:08

正如阿曼达所说，如果SET选项在ARITHABORT中有所不同，也许您应该看看dba.stackexchange.com/questions/9840/…

– ARA
2013年11月30日12:14

编程黑洞网

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

#1 楼

#2 楼

#3 楼

评论