由於高記憶體使用，SQL Server 查詢性能嚴重下降
從StackOverflow移動。
設想
我有一個 ASP.NET Core 5 (.NET 5) 項目，它處理 GPS 設備並每天生成大約 6-8GB 的數據。在過去的幾個月裡，我一直致力於改進這些數據的讀寫。對於閱讀，我認為，我已經完全刪除了 EF Core 並將其替換為 Dapper 和儲存過程。對於寫作，我仍在使用 EF Core，並且一直在優化列的數據類型、重新創建索引以及整體重建數據庫（到目前為止，升級腳本為 14K 行）。我已經設法將所有這些大小減少了 71%，並且增長應該會慢得多，可能每天 0.5-1GB。
所有這一切都很棒，但是有些大帳戶中有數千台設備，每天會產生數千條通知。我知道索引在這一點上已經達到了它們所能達到的水平，並且大多數熱門查詢在大約 95% 的時間裡都在尋找，但是我們遇到了一個問題，如果我們打開最大的六個帳戶一次，其中大約四個將幾乎立即載入，另外兩個將超時。刷新失敗的兩個總是超時。
如果我重新啟動 SQL Server，並重新載入失敗的兩個，它們將立即載入，但現在前四個中的兩個將失敗。基本上似乎是先載入的總是載入，其餘的將失敗。顯然，當重新啟動 SQL Server 時，記憶體會被刷新，直到它再次開始建構。所以這是某種記憶體/記憶體問題。我注意到一旦記憶體被填滿並嘗試從磁碟讀取，當驅動器可以達到 2.5-3GB/s 時，它的讀取速度約為 20MB/s。我讓其中一個失敗的查詢執行，然後在等待 10 分鐘後停止它，重新啟動 SQL Server，然後重試它，它在一秒鐘內完成。
該數據庫在我的開發電腦上執行，該電腦具有 Core i7 4790K (4C8T) @4.4GHz、32GB RAM 和 1TB 970 Pro、Windows 10 20H2 Pro。實時伺服器在 AWS 上，具有 EPYC 7571 4vC @2.2GHz、16GB 記憶體和 2TB、6000 IOPS、1000 MB/s gp3 卷（一旦數據庫重建和縮小，大小將減少到 512GB 或 768GB )，Windows Server 2019 1809。數據庫在兩者上都是 SQL Server 2019。
問題
當記憶體最終填滿時，我可以/應該做些什麼來解決超時/回歸？
回複評論 (1)
關於實時伺服器的更多資訊。它是
t3a.xlarge在 AWS 上的，我正在考慮t3a.2xlarge根據成本將其提高到的想法。它每晚重新啟動。這不是一個完美的配置，因為我還安裝了 MySQL（已禁用），以便我們可以將以前開發人員的原始數據庫導入 SQL Server。我知道它需要重建，但我現在或短期內沒有時間這樣做。@AMtwo，預設情況下我確實有最大記憶體，我將系統上的 32GB 更改為 28GB。LPIM 已關閉，因此我將其打開。
@JD，對於四個最大的帳戶，行數是：A 5,507、B 1401、C 318、D 220。奇怪的是，經過一夜之間的最新重建後，我在 ASP.NET 應用程序中打開了所有類型的帳戶，這些帳戶是（大約211個），除了A之外，所有的都開了。A是最大的賬戶，不幸的是訪問量最大的賬戶。截至目前，當從 ASP.NET Core 訪問它時，它會超時並執行較大的讀取峰值，但是當我使用 SSMS 執行完全相同的 SP 時，它會在大約 2 秒內正常載入，而讀取峰值要小得多。
請參閱任務管理器中的附加螢幕截圖。在所有其他帳戶都打開一次之後，我在 ASP.NET Core 中嘗試了這個大帳戶，它只是超時並且長時間讀取 100MB 以上，而在 SSMS 中，它在更短的時間內完成更小的 100MB 以上讀取，並在 2 內完成秒。重新啟動 SQL Server 後，記憶體被轉儲，在 ASP.NET Core 中打開此帳戶會導致 2-3 秒的處理時間，而磁碟讀取只有 1MB。記憶體消耗也幾乎沒有記錄。
我正在縮小數據庫，因為實時數據庫正在失控，相關成本也是如此。目前在主數據數據庫和 Serilog 數據庫之間，我們使用 929GB，每天增長 6-8GB. 重建的數據庫現在為 268GB，預計每天增長 0.5-1GB 或更少。所以，坦率地說，我並不特別關心你所說的關於縮小數據庫的危險信號。我需要能夠管理和控制數據庫的增長以及與之相關的成本。我不能只是不斷增加卷大小或最終更改實例大小並添加更昂貴的資源，只是為了能夠在重建和收縮做得更好時執行。值得一提的是，當我執行數據庫重建腳本時，應用程序也將被更新並完全關閉，傳入的數據將被重定向到 S3 並在它們重新聯機時導入回來，不會有讀取或寫入當時從應用程序到數據庫，所以我可以進行重建和縮小。
最終，我們將不得不實施歸檔以開始將舊數據從數據庫中轉儲出來，以保持其大小可管理，但這將在很長一段時間內發揮作用。
附上執行計劃。所有帳戶之間的唯一區別是
id被傳遞到 SP。@David Browne，我會調查的。
@RBarryYoung，當我瀏覽 ASP.NET Core 應用程序並轉到帳戶頁面時，我正在交替使用打開或載入。我認為執行計劃螢幕截圖涵蓋了您要查找的內容？
在重新啟動 SQL Server 之前。
重新啟動 SQL Server 後。
SP的執行計劃。
經過幾天閱讀 SQL Server 的內部結構後，我終於解決了我的問題。就是這樣：
正如*@FrancescoMantovani*在他的建議中所建議的那樣，我從**sp-Blitz開始。**它向我指出了一些有趣的事情，主要是我不應該在計算列上使用 UDF。我碰巧有許多 UDF 支持計算列，我認為這很好。我想當涉及到 UDF 時，我在 C# 中的思考比在 T-SQL 中更多。結果是計算列中的 UDF 會強制查詢執行進入串列模式，即使我沒有接觸計算列。假設 SQL Server 2019 應該為此實現性能改進，但我從未見過它，這可能意味著必須以非常特定的方式製作或使用 UDF。
因此，我刪除了幾乎所有的 UDF，並將它們內聯到計算列的定義中。有 3 個 UDF 會查詢其他表，因此對它們來說並不容易。我最終將他們正在查找的列添加到計算列所在的兩個表中，然後也內聯這些 UDF。
例如，我的
Addresses表有一個計算列FullText，它查詢Zones並Countries獲取它們的每個縮寫以組成完整地址。縮寫列現在復製到Addresses表中。雖然從哲學的角度來看，我不樂意有重複，但這是可行的折衷方案，所以我很滿意。我合併到更新觸發器中的最後一個 UDF 我必須計算和儲存列的值。接下來我不得不處理大量的隱式轉換。這些分為三個不同的類別，我想我會說：
- 將 a 傳遞
DATETIME2給SWITCHOFFSETwhich 需要 aDATETIMEOFFSET。偏移值也必須是NCHARorNVARCHAR或者那些也將被隱式轉換。我通過簡單地將所有DATETIME2列轉換為DATETIMEOFFSET我的升級腳本來解決這個問題。全面浪費每列兩個字節，但事實就是如此。如果數據類型有一個類似SWITCHOFFSET的函式會很好。DATETIME2無論如何，我將所有內容都儲存為 UTC，所以我只需要在將其呈現到使用者的時區時進行更改，無論它是什麼。FORMAT還需要格式字元串的NCHARorNVARCHAR值，所以我剛剛瀏覽並更新了所有這些。在我從查詢查找中傳遞值的情況下，我也將列更改N為。- 最後，我做了很多
CONCAT，這將是非列N和N列的混合，這也會導致隱式轉換。我決定讓數據庫中的幾乎所有列都成為NCHAR或NVARCHAR列，除了一些我知道永遠不會在CONCAT表達式中使用的列。現在隱式轉換不再是一件事，它有所幫助，但還不夠。原始文章中的查詢仍然給我同樣的麻煩。我很困惑。我會在應用程序中進行測試執行，超時，但在 SSMS 中它會繼續正常工作。
經過大量探勘，我不知何故偶然發現應用程序慢，SSMS 快？了解 Erland Sommarskog 的性能奧秘。通讀它，很明顯參數嗅探確實是根本問題。所以，@alroc，你說得對。我的印像是它只是我明確傳遞的參數，例如 SP 的參數，我竭盡全力將局部變數更改為內聯常量。我以為這會解決它，但它沒有。
看了Erland的文章，了解到參數嗅探還涉及到連接參數、數據庫設置、使用者設置，甚至SSMS設置。事實證明，SSMS 啟用
ARITHABORT了，這就是它與應用程序之間的參數差異，這就是為什麼它獲得不同的執行計劃，這些執行計劃的速度要快得多。在 SSMS 中禁用它並重新執行我的測試後，SSMS 最終匹配了應用程序並且速度很慢。分析執行計劃，我發現它推薦了一個與我認為不同的查詢的索引，這是問題所在。然後我花了很長時間找出正確的索引，雖然它有助於查詢的一部分，但新索引並沒有讓它更快。事實證明，有一個排序操作佔用了查詢的大部分時間，並且要訪問 tempdb 8 次。這是由 my
Notifications和Reports表之間的連接引起的，其中Report’ReceivedAtUtc列用於對結果進行排序。我嘗試為此添加一個索引，但它從來沒有幫助。最後，我決定添加一NotifiedAtUtc列Notifications並將其用作排序列。在我進行此更改後，查詢終於完美執行，並在大約 2 秒內載入到應用程序和 SSMS 中。記憶體消耗也終於減少了。在我打開所有 211 個有問題的帳戶後，記憶體使用量僅為 2.1GB，而*“盡可能多的記憶體，我可以使用和失敗”*之前。我的最終升級腳本也從 4 小時到 2 小時從這些更改之一執行，所以這是一個受歡迎的獎勵。最終重建的數據庫總計 264GB，所以我很高興我已經完成了優化和減少儲存消耗的目標，並且一次實際上具有良好的性能。
在過去的幾天裡，我讀到的關於 SQL Server 內部的資訊比我想像的或需要的要多。