分配更多 CPU 和 RAM 後 SQL Server 性能變慢

我們在虛擬 Windows 2008 R2 伺服器上執行 SQL Server 2008 R2 (10.50.1600)。在將 CPU 從 1 核升級到 4 核並將 RAM 從 4 GB 升級到 10 GB 後，我們注意到性能更差了。

我看到的一些觀察結果：

1. 執行時間小於 5 秒的查詢現在需要 >200 秒。
2. CPU 與 sqlservr.exe 的罪魁禍首掛鉤為 100。
3. 對具有 460 萬行的表執行選擇計數 (*) 需要 90 多秒。
4. 伺服器上執行的程序沒有改變。唯一的改變是增加cpu和ram。
5. 其他 sql 伺服器有一個靜態分頁文件，該伺服器設置為自行管理。

有沒有人遇到過這個問題？

根據 sp_BlitzErik，我跑了

EXEC dbo.sp_BlitzFirst @SinceStartup = 1;

給我這些結果。

這裡發生了很多事情，其中​​大部分內容非常廣泛和模糊。

1. 2008R2 RTM 於 2010 年 4 月 21 日問世。完全不受支持。您需要優先使用大約 3 年前發布的最新 Service Pack。這樣，如果您遇到奇怪的錯誤或其他問題，您將被覆蓋。前往這裡找出您需要下載的內容。
2. 由於您添加了 vCPU（從 1 到 4）並且沒有更改任何設置，因此您的查詢現在可以並行進行。我知道這聽起來他們都會更快，但堅持下去！
3. 您可能已經添加了 RAM，但您可能沒有更改 Max Server Memory，因此您的伺服器可以利用它。
4. 弄清楚您的伺服器正在等待什麼。我從事的一個開源項目提供了免費腳本來幫助您測量 SQL Server。如果您想嘗試一下，請前往這裡。

你會想抓住 sp_BlitzFirst 來檢查你的伺服器的等待統計資訊。您可以通過幾種方式執行它。

這將顯示您的伺服器自啟動以來一直在等待什麼。

EXEC dbo.sp_BlitzFirst @SinceStartup = 1;

這將在 30 秒的視窗內向您顯示正在等待的查詢。

EXEC dbo.sp_BlitzFirst @Seconds = 30, @ExpertMode = 1;

一旦你弄清楚正在等待什麼查詢（那裡有大量關於等待統計的東西），你就可以開始進行更改以控制事情。

如果您看到它們正在等待CXPACKET，這意味著您的查詢是並行的，並且可能會互相踐踏。如果你遇到了這個問題，你可能會考慮將並行的成本門檻值提高到 50，並且可能將 MAXDOP 降低到 2。

在此步驟之後，您想使用sp_WhoIsActive或 sp_BlitzWho（後者位於之前的 GitHub 儲存庫中）之類的東西開始擷取查詢計劃。除了等待統計數據之外，它們是您可以查看以找出問題所在的最重要的事情之一。

您可能還想查看 Jonathan Kehayias 撰寫的這篇關於VMWare Counters的文章，以查看與 SQL Server 相關的內容。

更新

查看等待統計數據和男孩，他們很奇怪。CPU肯定有問題。您的伺服器大部分時間都無聊地坐著，但是當事情變熱時，事情就會變得糟糕。我會嘗試輕鬆解決這個問題。

1. 你遇到了一個叫THREADPOOL. 你沒有很多，但這是有道理的，因為你的伺服器不是非常活躍。我會在一分鐘內解釋為什麼。
2. 你有很長的平均等待時間SOS_SCHEDULER_YIELD和CXPACKET。你在一個虛擬機上，所以你要確保 SQL Server 有保留，或者這個盒子沒有被可怕的超額訂閱。一個吵鬧的鄰居真的會毀了你在這裡的一天。您還需要確保伺服器/VM 來賓/VM 主機未在平衡電源模式下執行。這會使您的 CPU 減速到不必要的低速，並且它們不會立即恢復到全速。
3. 他們是如何联系在一起的？使用 4 個 CPU，您有 512 個工作執行緒。請記住，單個 CPU 的數量相同，但現在您的查詢可以並行，它們可以消耗更多的工作執行緒。在您的情況下，並行查詢的每個並行分支有 4 個執行緒。

什麼是平行的？最有可能的一切。Parallelism 的預設成本門檻值是 5。在 90 年代後期的某個時候，在看起來像這樣的桌面上工作時，這個數字被設為預設值。

誠然，您的硬體比大多數筆記型電腦都要小，但您仍然領先於這件事。

當大量並行查詢開始執行時，您將用完這些工作執行緒。發生這種情況時，查詢只是等待執行緒開始執行。這也是SOS_SCHEDULER_YIELD進來的地方。查詢正在退出 CPU 並且很長一段時間都沒有重新啟動。我沒有看到任何阻塞等待，因此您很可能只是忙於查詢內並行等待。

你能做什麼？

1. 確保沒有任何東西處於平衡功率模式
2. 將 MAXDOP 更改為 2
3. 將並行度的成本門檻值更改為 50
4. 按照上面的 Jon K. 文章驗證 VM 執行狀況
5. 使用呼叫的腳本sp_BlitzIndex查找任何失去的索引請求。

如需更徹底的故障排除，請查看我為 Google 撰寫的關於雲中硬體大小的白皮書。

希望這可以幫助！

引用自：https://dba.stackexchange.com/questions/187207