高 CPU 和計劃記憶體不穩定
在過去的幾個月裡，我的 SQL Server 實例的 CPU 使用率比以前高，有時會達到 100% 並影響應用程序。
在那段時間裡，最近沒有對任何 SQL Server 配置或選項進行任何更改，作業系統可能在更新檔方面有，但那是另一個團隊領域，在過去幾個月之前它不是這樣執行的。當我們有高CPU 使用的持續時間，我看到的主要是 CXPACKET、LCK_M_X、THREADPOOL、LATCH_EX、RESOURCE_SEMEPHOR_QUERY_COMPILE 的等待統計資訊。CXPACKET 的並行性是迄今為止最大的。
我喜歡執行 Brent Ozar 的 sp_blitzcache，最突出的一點是計劃記憶體似乎被某些東西清除了，並且 99% 的計劃是在最後一小時內創建的。這表示這可能是記憶體壓力或計劃記憶體不穩定。我不確定要檢查什麼來查找位置。
我曾想過啟用 LPIM，但現在還沒有，但除此之外不確定要嘗試什麼，我將不勝感激任何幫助。
編輯** 我已經開始將 sp_whoisactive 記錄到表中。我發現應用程序有一個長時間執行的程序，它在數據庫上執行一個非常密集的非規範化程序，逐漸建構和建構，直到它最終開始重建索引的階段。
顯然，這是殺死伺服器性能的原因。我們已確保它不會再次執行。
然而，業務對於應用程序/伺服器變得非常緊張。星期五有一段時間，CPU 再次達到 100% 的最大值大約 10 分鐘，不像以前持續時間更長，這次它在 15 分鐘後停止。通過查看 sp_whoisactive 的結果，我可以看到在 CPU 被最大化之前，我開始看到大量的 RESOURCE_SEMAPHORE_QUERY_COMPILE 等待類型，這些等待類型持續了大約 10 分鐘。我有點理解這種等待類型是什麼，但這是否導致了這段時間的高 CPU，因為某種執行緒等待的累積效應？
如果是這樣，我該如何解決這個問題，以免再次發生？我沒有任何調整查詢/IX 的空間，添加更多記憶體有幫助嗎？
***** 編輯 2 ***** 對此的更新似乎最初的問題是另一個團隊開始在數據庫上執行非規範化程序，該程序非常密集並且剛剛殺死了伺服器。在他們停止這樣做之後，我收到了很少的 RESOURCE_SEMAPHORE_QUERY_COMPILE 等待類型，這與以前不同
情況比以前好多了，但是 CPU 有時仍然達到 100% 的時間比我想要的要長，而且我仍然遇到計劃記憶體不穩定的問題，它一直在被修剪。
瀏覽此網頁後 - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6b9118f3-b6c1-4f39-ae80-d149392656f9/sql-server-2016-memory-pressure-leads-to- the-plan-cache-clearing?forum=sqldatabaseengine我意識到作業系統級別的某些東西發生了變化，肯定需要更多記憶體，導致記憶體壓力，然後它從sql server和計劃記憶體中佔用記憶體資源。
我降低了我的實例最大伺服器記憶體設置，事情已經穩定了很多，CPU 整體顯著降低並且很少達到 100%，並且計劃記憶體的大小很好，並且從未被顯著削減。
這個DBA.StackExchange 答案提供了一個開始跟踪問題的好地方。
特別是連結的Microsoft Docs - SQL Server 2008文件中的計劃記憶體（雖然有點過時，但仍然有用）有一個名為**“批量重新編譯的計劃穩定性相關原因”的部分，詳細說明了可以清除計劃記憶體的所有原因（您應該另請閱讀上一節重新編譯的原因以**獲取一些有用的資訊）。
從上面的文章中引用的理由太多了，但我覺得特別有趣並且可能是最容易開始的一個部分是以下內容：
以下操作會刷新整個計劃記憶體，因此會導致之後第一次送出的批次重新編譯：分離數據庫、將數據庫升級到 SQL Server 2005、將數據庫升級到 SQL Server 2008、恢復數據庫、DBCC FREEPROCCACHE 命令，RECONFIGURE 命令，ALTER DATABASE … MODIFY FILEGROUP 命令，修改排序規則，使用 ALTER DATABASE … COLLATE 命令
以我的經驗，我個人看到開發人員安排命令，例如
DBCC FREEPROCCACHE定期執行並導致類似問題。（此外，我還看到整個數據庫中過度使用OPTION (RECOMPILE)and子句也會導致類似問題。）WITH RECOMPILE如果您可以消除上述清單，那麼您應該遵循“**批量重新編譯的計劃穩定性相關原因”**文件中的指南的其餘部分，也許接下來看看你們執行會導致計劃記憶體重新編譯的 DDL 查詢的頻率，然後如何你們經常全面更新統計數據。
不幸的是，這很可能會成為一個消除遊戲，並且不會是一個需要解決的微不足道的問題，但祝你好運。
檢查 BlitzCache 結果中的“許多一次性計劃”警告。您看到的行為的一種可能性是應用程序現在正在向 SQL Server 發送大量未參數化或參數化不一致的查詢。這通常是 ORM 的情況（例如）。
這導致那些記憶體的執行計劃消耗越來越多的記憶體。隨著時間的推移，這種記憶體壓力會迫使其他計劃記憶體不足，從而導致更多的重新編譯和更高的 CPU 使用率。
為了緩解這種情況，您可以啟用伺服器級設置“針對臨時工作負載進行優化”：
EXEC sp_configure 'Show Advanced Options', 1; GO RECONFIGURE GO EXEC sp_configure 'optimize for ad hoc workloads', 1 GO RECONFIGURE GO該設置的作用是它僅在第一次執行時儲存執行計劃的“存根”。與完整的記憶體計劃相比，這佔用了少量記憶體。如果它再次被使用，完整的計劃將被記憶體（因此在第二次執行後，正確參數化的查詢仍將受益於記憶體）。