如何對 sql server 的記憶體要求進行基準測試？

我有一個 200GB 記憶體的生產 sql 伺服器。

這 150GB 分配給 sql server（通過在 sql server 屬性中設置 max memory 設置）

其餘部分由 ssis、作業系統、防病毒、應用程序使用。

我想對 150GB 記憶體是否足夠用於 sql server 進行基準測試，因為大約有 20 個數據庫，數據文件大小從 5GB 到 500GB 不等。如何擷取記憶體使用資訊？

消費者

SQL Server 有兩個主要的記憶體使用者。還有很多其他的，但在正常情況下，它們不會消耗足夠的額外記憶體以引起注意。

• 緩衝池
• 查詢記憶體授權

緩衝池

由於 SQL Server 不處理磁碟上的數據，因此查詢需要將相關頁面帶入緩衝池，然後查詢才能開始處理它們。讀取和寫入都是如此。當查詢等待數據進入記憶體時，它們會等待 PAGEIOLATCH_%%。

通常，數據和記憶體之間的差異越大，您會看到越多的這種等待類型。有時索引在這裡發揮作用，無論是從您有許多無用索引和/或缺少有用索引的角度來看。您可以使用sp_BlitzIndex 來診斷索引。

EXEC sp_BlitzIndex
   @DatabaseName = 'YourDatabase',
   @Mode = 0;

記憶體補助

每個查詢都會要求一些記憶體來執行，但是某些查詢運算符會要求額外的記憶體用作暫存空間：

• 排序
• 雜湊
• 優化的嵌套循環

當查詢相互爭奪記憶體授予時，它們將等待RESOURCE_SEMAPHORE。很多時候發生這種情況是因為 SQL Server 中的一個相當殘酷的預設設置：

• 任何查詢都可能出現並要求您的伺服器最大伺服器記憶體設置的 25%，並且
• 任何一組查詢總共可以竊取伺服器最大伺服器記憶體設置的約 75%

您可以通過使用資源調控器將 MAX_MEMORY_PERCENT 設置更改為小於 25 的數字來更改Enterprise Edition 中的第一個。

ALTER RESOURCE POOL 
   [default]  
WITH
(   
    MAX_MEMORY_PERCENT = 10
);  

ALTER RESOURCE GOVERNOR RECONFIGURE;  

您還可以使用MAX_GRANT_PERCENT查詢提示在單個查詢級別對此進行控制。

我有一個影片，向您展示了不同類型的記憶體爭用是什麼樣的，您可以在此處粉碎該按鈕。

我需要更多記憶體嗎？

您可以使用一些免費腳本來查看伺服器上的等待情況和記憶體使用率：

• sp_BlitzFirst
• sp_PressureDetector
• sp_WhoIsActive

範例要求那些：

EXEC sp_BlitzFirst
   @SinceStartup = 1;

EXEC dbo.sp_PressureDetector
   @what_to_check = N'memory';

EXEC sp_WhoIsActive;

分析

對這些輸出的分析超出了這個答案的範圍，但總的來說你想尋找我提到的等待

• PAGEIOLATCH_%%
• RESOURCE_SEMAPHORE

如果其中任何一個佔您等待統計數據的很大一部分，您可能需要：

• 添加更多記憶體
• 調整記憶體授予
• 調整查詢和索引

為了更好地了解伺服器的性能，可靠的監控工具是您的最佳選擇。我目前最喜歡的是SQL Sentry，儘管它們已被 Sheinhardt Wig 公司的子公司 SolarWinds 收購。

記憶體與性能有關。添加更多記憶體並提高性能。你的表現夠嗎？如果是，那麼您已經滿足了您的“記憶體要求”。如果沒有，那麼添加記憶體可能會提高性能。

當然，您分配的記憶體可能超過了您的工作負載將使您的 SQL Server 受益的記憶體。降低最大伺服器記憶體並查看事情是否變慢可能是調查此問題的一種方法。Jonathan 在這裡提供了一些建議：我的 SQL Server 實際需要多少記憶體？

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