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

事变区内存授权不是您的案例中的一个身分,但它值得相识.

数据会见

（编辑：湖南网）

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