UAT和PROD服务器执行计划的不同

Question

我想了解为什么在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 ) ) 

ON 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.

On 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要创建一个worker表，而PROD上却没有？它在UAT而不是PROD上创建一个表假脱机。另外，为什么UAT和PROD的执行时间如此不同？

注意：

我正在这两台服务器上运行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 :最大内存60 max。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上的最大内存从60 MB更改为7680 MB，我可以在PROD中生成相同的计划。查询与UAT同时完成。

现在我要明白-为什么？而且，通过这个，我将无法证明这个怪物服务器可以取代旧服务器！

Answer 1

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

工作空间内存

对于包含诸如排序和散列之类的占用内存的迭代器的计划，缓冲池的大小(以及其他事情)决定了在运行时可能对查询可用的最大内存授予量。

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

估计的可用内存授权是查询优化器使用的成本模型的输入。因此，需要大型排序或散列操作的计划替代方案在设置较大缓冲池的计算机上比在设置较低的计算机上更有可能被选择。对于内存容量很大的安装，成本模型可能会在这种思维方式上走得太远--选择具有非常大种类或哈希类型的计划，而选择替代策略(KB2413549 -使用大量内存可能导致Server TF2335中的低效计划)则更为可取。

工作区内存分配在您的情况下不是一个因素，但它是值得了解的。

数据访问

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

问题中显示的查询计划中的聚集索引扫描是重复访问的一个例子；对于嵌套循环半连接的每一次迭代，扫描都会被重绕(重复，不改变相关参数)。半连接的外部输入估计为28.7874行，这些扫描的查询计划属性显示结果估计为27.7874。

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

其效果是，配置较高的最大缓冲池大小的安装将倾向于降低扫描(或查找)的成本，这些扫描(或查找)会多次读取相同的页面，而不是使用更小的最大缓冲池大小的安装。

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

尽管如此，问题中的计划似乎表明，在重复扫描和创建临时索引之间的选择是相当平衡的。在具有较大缓冲池的机器上，重复扫描的成本略低于创建索引。在缓冲池较小的机器上，扫描成本降低了更小的数量，这意味着优化器的索引假脱机计划看起来要便宜一些。

计划选择

优化器的成本模型做出了许多假设，并包含了大量的详细计算。跟踪所有细节并不总是(甚至通常)可能的，因为并不是所有我们需要的数字都公开了，而且算法可能在不同版本之间发生变化。特别是，考虑到遇到缓存页面的可能性而应用的缩放公式并不为人所知。

在这种特殊情况下，优化器的计划选择无论如何都是基于不正确的数字。聚集索引查找的估计行数为28.7874，而在运行时遇到的行数为256行，几乎是一个数量级。我们无法直接看到优化器拥有的关于这28.7874行中的预期值分布的信息，但这也很可能是可怕的错误。

当估计是错误的时，计划选择和运行时性能从本质上说并不比机会好。与重复扫描相比，带索引假脱机的计划执行得更好，但是认为增加缓冲池的大小是异常的原因是完全错误的。

如果优化器有正确的信息，那么它产生良好的执行计划的可能性就会大得多。内存较多的实例在工作负载上的表现通常要好于内存较少的另一个实例，但没有保证，特别是当计划选择基于不正确的数据时。

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

Answer 2

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

此外，非常感谢@swasheck首先发现了这个问题。

向微软和下面的公司打开了一个案例是什么建议。

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

KB2413549 -使用大量内存可能导致Server中的低效计划更详细地描述了它。

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

Answer 3

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

此外，我注意到您的"set“选项在每个环境中都是不同的：

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 on Prod：

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

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