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

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