sql-server-2008-r2 – 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.

(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,lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256,logical reads 9472,lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1,logical reads 100,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.

关于PROD：

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

(3 row(s) affected)
Table 'PORTFOLIO'. Scan count 256,logical reads 21698816,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.

请注意,在PROD上,查询建议缺少索引,这在我测试时是有益的,但这不是讨论的重点.

我只想了解：
ON UAT – 为什么sql server创建一个worker表而在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

更新：

UAT执行计划XML：

http://pastebin.com/z0PWvw8m

PROD执行计划XML：

http://pastebin.com/GWTY16YY

UAT执行计划XML – 从PROD生成计划：

http://pastebin.com/74u3Ntr0

服务器配置：

PROD：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同时完成.

现在我需要明白 – 为什么？另外,通过这个,我无法证明这个怪物服务器能够取代旧服务器！

解决方法

工作区内存

对于包含内存消耗迭代器(如排序和散列)的计划,缓冲池的大小(以及其他内容)确定查询在运行时可用的最大内存授予量.

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

估计的可用内存授予是查询优化器使用的成本模型的输入.因此,在具有较大缓冲池设置的计算机上比在具有较低设置的计算机上更可能选择需要大型排序或散列操作的计划备选方案.对于具有大量内存的安装,成本模型可能会因为这种想法而走得太远 – 选择具有非常大的排序或散列的计划,其中替代策略将是更可取的(KB2413549 – Using large amounts of memory can result in an inefficient plan in SQL Server – TF2335).

工作区内存授权不是您的案例中的一个因素,但它值得了解.

数据访问

（编辑：淮安站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!