從 SQL Server 2005 遷移到 2012 後的性能問題

September 21, 2015

上週五，我將 SQL Server 實例從 2005 企業版遷移到安裝了 SQL Server 2012 企業版的全新 Windows Server 2012 伺服器。從那時起，使用者開始抱怨應用程序的性能。
以下是有關伺服器的一些資訊：
VMWare 5.5 上的虛擬伺服器
4 個 vCPU 和 24 Gb RAM。在以前的配置中，10 Gb 是必需的，但 tempdb 數據庫比我設置的要小（幾乎 6 Gb）
我現在已將最大記憶體目標設置為 22 Gb，並且 tempdb 完全在緩衝池中
遷移後（使用數據庫鏡像執行），我執行了更新統計資訊、索引重建和其他維護命令
我不知道在哪裡可以找到答案。如果除 SQL Server 實例之外的所有內容都相同（VM、磁碟等），那麼對我來說，問題出在 SQL Server 配置中，但在哪裡？我嘗試了一些更改，但似乎沒有一個有用。
你有什麼想法？
來自評論的附加資訊
我執行了 sp_Blitz，它給了我一些資訊，但 SQL 2005 並沒有什麼新東西（例如儲存速度慢）
記憶體消耗（20Gb + ）主要是由於 tempdb（6Gb），其餘大部分由應用程序數據庫佔用
PLE 流量有時會下降到 150。在一天開始時，大部分數據都超出了記憶體
以前伺服器上的最大記憶體設置為 10Gb
我們使用 Idera 診斷管理器
最大伺服器記憶體設置為前一個伺服器上的值的兩倍。
頁面預期壽命每天至少兩次降至 150 以下
sp_BlitzIndex分析
我開始使用 sp_BlitzIndex 進行分析，除了寫得不好的程式碼之外，它還顯示了這一點：
激進索引：總鎖定等待時間 > 5 分鐘（行 + 頁）
dbo.TABLE1.PK_TABLE1 (1): 
Row lock waits: 3,591; total duration: 10 minutes; avg duration: 0 seconds; 
Page lock waits: 23; total duration: 15 seconds; avg duration: 0 seconds; 
Lock escalation attempts: 510,489; Actual Escalations: 1.

dbo.TABLE1.I_TABLE1_CNTTYPE_CATEGORY_IS_CURRENT (85): 
Row lock waits: 155; total duration: 48 seconds; avg duration: 0 seconds;
Page lock waits: 129; total duration: 8 minutes; avg duration: 4 seconds;
Lock escalation attempts: 29,423; Actual Escalations: 4,951.
只是創建一個額外的索引會對該現象產生一些變化嗎？
sp_configure
我已按要求比較了 sp_configure 的輸出。以下是不同之處：
Config                          Old     New
Blocked process threshold       0       120
Maximum Degree of parallelism   0       4
Maximum Memory (MB)             10240   22000
電源選項已經處於高性能狀態。我使用以下命令將記憶體設置回 10 Gb：
CHECKPOINT ;
DBCC DROPCLEANBUFFERS ;
EXEC sys.sp_configure N'max server memory (MB)', N'10240'
GO
RECONFIGURE WITH OVERRIDE
GO
使用 10Gb RAM 執行一小時後：最後一個區別是 tempdb 的大小，它比舊伺服器上的大，現在使用了大部分記憶體，導致 Page Life Expectancy 幾乎沒有達到 490。
診斷管理器 CPU 統計報告分析
CPU 統計報告：
平均 SQL 編譯為 500
平均 SQL 重新編譯 120
每分鐘最多 10 次鎖定等待，平均 5 次
大多數情況下，表鎖升級平均為 40。
我已經為最常用的使用者數據庫設置了針對臨時工作負載伺服器設置的優化，甚至設置了“強制參數化”。
到目前為止，沒有人告訴我有性能改進。由於它是我返回的數據庫並且不是由 DBA 團隊管理的，因此我們沒有背景來檢查它是否恢復正常…
我會等待一段時間，看看現在是否可以。感謝大家的幫助！

最可能的解釋是，由於統計資訊、索引大小和密度以及配置設置的變化，您的查詢正在新伺服器上使用不同的執行計劃執行。
對查詢優化器執行計劃選擇影響最大的配置設置是：
max server memory
max degree of parallelism
cost threshold for parallelism
問題指出，新安裝的記憶體翻了一番，所以這很可能是主要因素。直覺地說，我們可能期望更多的記憶體總能提高性能，但情況並非總是如此，因為優化器可能會開始支持具有更多散列、排序和掃描的計劃，而不是嵌套循環和索引搜尋。
更多資訊請看我之前的回答：
UAT 和 PROD 伺服器上執行計劃的差異
20GB 的設置通常不足以證明設置啟動跟踪標誌 2335 是合理的（如該答案中所引用），但只有測試才能證明這一點。
作為一種潛在的快速修復，您可能會考慮設置max server memory回以前的值，同時您可以辨識在新伺服器上回歸的查詢計劃，並使用正常的調整方法來糾正根本原因。如果這聽起來很籠統，我很抱歉，但現實情況是，性能不佳有很多可能的原因，並且有確定的方法來辨識和糾正最常見的原因。我在這裡回答的目標是讓您的系統恢復到接近 2005 年的位置。
使用 10Gb RAM 執行一小時後：最後一個區別是 tempdb 的大小，它比舊伺服器上的大，現在使用了大部分記憶體，導致 Page Life Expectancy 幾乎沒有達到 490。
tempdb數據庫之前可能已經增長，以適應最終從記憶體溢出的排序和散列；它現在可能不需要那麼大。tempdb數據庫使用記憶體的方式與任何其他數據庫沒有任何不同。PLE“幾乎沒有 490”作為英語句子毫無意義。
將最大伺服器記憶體設置回 10GB 的重點是鼓勵類似於 2005 安裝的執行計劃，從而將性能恢復到可接受的水平。你不說它是否有幫助。在這個階段你很可能需要當地的專業支持；我認為我們已經達到了問答形式可以合理完成的極限。