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.
在產品上：
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產品：最大記憶體 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：
產品執行計劃 XML：
UAT 執行計劃 XML - 從 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 - 使用大量記憶體可能會導致SQL Server 中的低效計劃 - TF2335）。
在您的情況下，工作空間記憶體授予不是一個因素，但值得了解。
數據訪問
緩衝池的潛在大小也會影響優化器的數據訪問成本模型。模型中的一個假設是每個查詢都以冷記憶體開始 - 因此假設第一次訪問頁面會產生物理 I/O。該模型確實試圖考慮重複訪問來自記憶體的可能性，這個因素取決於緩衝池的潛在大小等。
問題中顯示的查詢計劃中的 Clustered Index Scans 是重複訪問的一個範例；對於嵌套循環半連接的每次迭代，掃描都被倒帶（重複，不改變相關參數）。半連接的外部輸入估計有 28.7874 行，這些掃描的查詢計劃屬性顯示估計的迴繞結果為 27.7874。
同樣，僅在 SQL Server 2012 中，計劃的根迭代器顯示該
Estimated Pages Cached部分中的數量Optimizer Hardware Dependencies。該數字報告了成本計算算法的輸入之一，該算法看起來考慮了來自記憶體的重複頁面訪問的機會。其效果是，與具有較小最大緩衝池大小的安裝相比，具有更高配置的最大緩衝池大小的安裝將傾向於降低多次讀取相同頁面的掃描（或查找）成本。
在簡單的計劃中，通過與估計的操作員成本進行比較，可以看出倒帶掃描的成本降低
(estimated number of executions) * (estimated CPU + estimated I/O)，後者會更低。由於半聯接和聯合的影響，範例計劃中的計算更加複雜。儘管如此，問題中的計劃似乎顯示了重複掃描和創建臨時索引之間的選擇非常平衡的情況。在具有較大緩衝池的機器上，重複掃描的成本略低於創建索引。在具有較小緩衝池的機器上，掃描成本減少的量較小，這意味著索引假離線計劃對於優化器來說看起來稍微便宜一些。
計劃選擇
優化器的成本模型做了很多假設，並且包含了大量的詳細計算。並非總是（甚至通常）可以跟踪所有細節，因為並非我們需要的所有數字都暴露出來，並且算法可以在版本之間更改。特別是，用於考慮遇到記憶體頁面的機會的縮放公式並不為人所知。
在這種特殊情況下更重要的是，優化器的計劃選擇無論如何都是基於不正確的數字。Clustered Index Seek 的估計行數為 28.7874，而在執行時遇到 256 行 - 幾乎是一個數量級。我們無法直接看到優化器所擁有的關於這 28.7874 行中值的預期分佈的資訊，但它也很可能是非常錯誤的。
當估計出現這種錯誤時，計劃選擇和執行時性能基本上並不比機會好。帶有索引假離線的計劃恰好比重複掃描執行得更好，但是認為增加緩衝池的大小是導致異常的原因是完全錯誤的。
在優化器擁有正確資訊的情況下，它產生一個體面的執行計劃的機會要大得多。具有更多記憶體的實例在工作負載上的性能通常比具有更少記憶體的另一個實例更好，但不能保證，尤其是當計劃選擇基於不正確的數據時。
這兩個實例都以自己的方式提出了缺失的索引。一個報告了明確的缺失索引，另一個使用了具有相同特徵的索引線軸。如果索引提供良好的性能和計劃穩定性，那可能就足夠了。我也傾向於重寫查詢，但這可能是另一回事。