SQL Server 2005 全球減速
兩天前，我們的生產伺服器遭受了嚴重的減速，主要症狀是異常大量的請求正在遭受 SQLTimeouts。我將快速描述我們的設置、我調查的內容、我們的解決方法，然後提出我的問題。
我們的設置
一對伺服器託管我們的 SAS 應用程序的這個分支。一個是在 IIS 上執行多個應用程序的應用伺服器，另一個是執行 SQL Server 2005 的 Windows Server 2008 機器。SQL 託管在 100 到 200 個數據庫之間。
問題/調查
服務幾乎停止了。一些請求通過，但大多數遭受 SQL 超時。SQL 機器 CPU 和 RAM 看起來不錯，平均大約 25% 的 CPU 工作負載和 85% 的 RAM。當時我沒想過要檢查磁碟活動，因為我直接去了“EXEC sp_who2”
結果顯示數百個任務被 ID 123 阻塞，它本身和一百個其他任務被 ID 456 阻塞。正常執行通常根本沒有阻塞任務。當我在 15-20 秒後重新執行 sp_who2 時，會彈出不同的阻塞 ID，但阻塞/阻塞任務的數量似乎保持不變。（由於緊急模式，沒有計算組）
大多數任務都被諸如“SELECT INTO”或“CREATE INDEX on temptable”之類的語句阻塞。
解決方法
殺死 SQL 程序並重新啟動它以恢復服務。放緩沒有再次出現，但我們知道我們處於危險之中。
我的問題
我能做些什麼來解決這個問題，最好是在它再次發生之前？
子問題：
- 在正常活動期間我可以調查其他路徑嗎？
- 如果/當問題再次發生時，我應該收集哪些資訊？（需要快速獲取，因為這意味著我們將再次遇到服務中斷）
到目前為止我做了什麼
從症狀來看，我們懷疑問題是 tempdb 上的某種爭用。（另一個症狀是在問題期間右鍵點擊 tempdb 以查看屬性會在片刻後產生錯誤）
沒有日誌表明 tempdb 上發生了自動增長事件，但據我所知，沒有記錄自動增長成功，只有失敗。
從那時起，我已經閱讀了很多關於 tempdb 爭用的不同資訊來源，不僅限於但包括：
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/
據我所知，最好的做法是設置初始大小的 tempdb 文件，並且每個核心有一個，最多 8 個文件。我們計劃盡快將其落實到位（8 個核心，因此 8 個文件），因為這是最佳實踐。它們都在同一個硬碟上（目前），但我們認為最壞的情況是沒有改善，最好的情況是我們獲得了邏輯爭用瓶頸和磁碟 I/O 瓶頸之間的差異。
但是，我們無法確定與我們遇到的問題的相關性。據我了解，拆分為多個臨時文件將有助於“PAGELATCH_XX”類型的等待，但在正常活動期間執行 Paul S. Randal 的查詢（參見第一個發布的連結），不存在這種類型的等待。我在正常活動中看到的前 3 個是：
CXPACKET 68.63%
LATCH_EX 18.46%
PAGEIOLATCH_SH 4.35%
我無法知道減速期間發生了什麼類型的阻塞，因為那時我們沒有所有這些資訊。
在我發布此問題後的第二天，問題最終再次出現。
執行 Paul S. Randal 的查詢，我很快發現許多 PAGELATCH_XX 阻塞等待正在進行，因此使用 sp_who2 我能夠找到罪魁禍首數據庫，並且只從 Web 伺服器重新啟動相關的客戶端應用程序池，作為一種不那麼苛刻的解決方法恢復服務。
我們還能夠跟踪實際操作，這些操作比他們之前所做的更多 tempdb 工作，並將尋求以不同的角度解決這個問題。
解決方案
我們已經按照最佳實踐建議將 tempdb 文件拆分為多個文件，因為似乎這是解決我的問題的解決方案所發生的正確類型的爭用。