SQL Server 高 CPU 使用率和 RESOURCE_SEMAPHORE 等待
上週在其中一個 SQL Server 上出現問題，CPU 開始燃燒超過 80%（正常為 10-30%）
這持續了大約 2 小時，直到我手動故障轉移到 AG 中的輔助副本（這已經解決了問題）
問題開始：12:15
問題結束：14:15（手動 AG 故障轉移後）
伺服器資訊：
SQL Server 2017 32 logical processors (max DOP = 8) 256 GB RAM (Max Server Memory = 180 GB, used 179 GB)問題開始前與問題開始後相比，以下指標沒有明顯變化
- 使用者連接數/秒（平均 200-300）
- 批處理請求/秒（平均 200 次及以下）
- 數據庫記憶體記憶體（~150 GB）
低於指標峰值顯著，這對於該伺服器來說並不典型（通常這些指標很低）：
- 中央處理器 ( 超過 80 % )
- 記憶體授予待定
- 鎖定等待/秒，平均。鎖定等待時間，死鎖
- 鎖存等待時間
- 授予的工作區記憶體和保留的伺服器記憶體
查詢：
當問題開始時，我沒有註意到此伺服器的工作負載發生變化
開發人員還確認應用程序完成了他們通常的工作並且正在執行通常的查詢，應用程序負載沒有峰值
在這個“高 CPU 使用率”問題期間，CPU 的前 10 個查詢看起來並不異常
即使 CPU 正常，我們通常看到的前 10 個查詢都是相同的（10-30 %）
問題：
問題似乎出在幾個相關的儲存過程中，該應用程序通常執行 1-4 次/秒，並且通常在 50 毫秒內完成，但是在問題期間，任何時候我檢查過 sys.dm_exec_requests（也使用了
exec ViewSessionsConnections 'running'https ://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/Sessions%20-%20ViewSessionsConnections.sql），有來自 1 個應用程序的 50-70 個會話，所有這些都試圖完成上述程序，而且速度很慢在監控工具中按持續時間查看前 10 個查詢時，前 1 和 2 是上述過程中的兩條語句 - 它們沒有消耗大量 CPU，但有過多的等待（RESOURCE_SEMAPHORE、LCK_M_IS）
通常這些在 10 毫秒或更短的時間內完成，每秒執行 1-4 次並且不會引起任何問題，現在這些開始每 1 次執行的持續時間為 4000-8000 毫秒，這就是問題所在
RESOURCE_SEMAPHORE 絕對不是此伺服器的典型情況，但在問題期間，它處於最高等待狀態（RESOURCE_SEMAPHORE - 等待授予記憶體的查詢；2 小時內總計 135400234 毫秒；平均 4174 毫秒）
Granted Workspace Memory在 SQL Server 中，Reserved System Memory在問題期間從 0 GB 飆升至 ~110 GB問題：
- 你對上面有什麼想法和經驗？
- 常量 RESOURCE_SEMAPHORE 等待和 Memory Grants Pending 是否會導致 CPU 壓力僅僅是為查詢分配工作空間記憶體？因為在問題期間查看 CPU 的前 10 個查詢時，CPU 數量看起來正常/正常
- 鑑於一直是 180 GB 和~ 150 GB ，問題開始時怎麼會這樣
Granted Workspace Memory並開始消耗 ~ 112 和 110 GB？它是否過度使用記憶體或類似的東西？Reserved Server Memory``Max Server Memory``Database Cache Memory remained- 為什麼通常在幾個月內 10 毫秒內完成的 SP 中的語句會開始經歷 RESOURCE_SEMAPHORE 等待並在 4000-8000 毫秒內完成？
- 如何在不手動故障轉移到輔助副本的情況下以更外科手術的方式解決問題？如何讓查詢平靜下來並將其恢復到 10 毫秒？需要刪除計劃，或者重新編譯查詢等？最好的方法是什麼？
- Brent Ozar First Responder Kit 或其他診斷程序 - 在性能問題期間應按什麼順序執行，以便更好地了解發生了什麼？
你對上面有什麼想法和經驗？
為什麼通常在幾個月內 10 毫秒內完成的 SP 中的語句會開始經歷 RESOURCE_SEMAPHORE 等待並在 4000-8000 毫秒內完成？
糟糕的計劃造成的 CPU 壓力。您應該使用Query Store跟踪和管理計劃穩定性，以及調查不良計劃並使用額外的索引和統計資訊進行補救，並可能對查詢進行更改。
常量 RESOURCE_SEMAPHORE 等待和 Memory Grants Pending 是否會導致 CPU 壓力僅僅是為查詢分配工作空間記憶體？
不，是相反的。糟糕的計劃是資源密集型的，會導致大量記憶體分配和 CPU 使用。