伺服器記憶體組態選項

適用于：SQL Server

SQL Server Database Engine 的記憶體使用率會受到一對組態設定所限制，最小伺服器記憶體 (MB) 和最大伺服器記憶體 (MB) 。 經過一段時間且在正常情況下，SQL Server會嘗試宣告記憶體，上限為伺服器記憶體上限 (MB) 。

注意

資料行存放區索引 和 記憶體內部 OLTP 物件有自己的記憶體 Clerk，可讓您更輕鬆地監視緩衝集區使用量。 如需詳細資訊，請參閱 sys.dm_os_memory_clerks。

在舊版SQL Server中，記憶體使用率幾乎未套用，表示SQL Server所有系統記憶體都可供使用。 建議在所有版本的 SQL Server，藉由設定最大伺服器記憶體 (MB) 來設定SQL Server記憶體使用率的上限。

• 自 SQL Server 2019 (15.x) 起，Windows 伺服器中的 SQL 安裝程式會根據安裝時可用系統記憶體的百分比，針對獨立SQL Server實例提供最大伺服器記憶體 (MB) 的建議。
• 您可以隨時透過最小伺服器記憶體 (MB) 和最大伺服器記憶體 (MB) 組態選項，針對實例 SQL Server所使用的SQL Server進程，以) MB 為單位重新設定記憶體 (界限。

注意

本指南指的是 Windows 上的 SQL Server 實例。 如需 Linux 中記憶體組態的資訊，請參閱Linux 上的 SQL Server和memory.memorylimitmb 設定的效能最佳做法和設定指導方針。

建議

這些選項的預設設定和最小允許值如下：

選項	預設	允許的最小	建議
最小伺服器記憶體 (MB)	0	0	0
最大伺服器記憶體 (MB)	2,147,483,647 MB	128 MB	其他進程未耗用 75% 的可用系統記憶體，包括 其他實例。 如需更詳細的建議，請參閱 最大伺服器記憶體。

在這些界限內，SQL Server可以根據可用的系統資源動態變更其記憶體需求。 如需詳細資訊，請參閱動態記憶體管理。

• 將最大伺服器記憶體 (MB) 值設定為太高，可能會導致單一實例SQL Server與裝載于相同主機上的其他SQL Server實例競爭記憶體。
• 不過，設定最大伺服器記憶體 (MB) 太低是遺失效能的機會，而且可能會導致SQL Server實例中的記憶體壓力和效能問題。
• 將最大伺服器記憶體 (MB) 設定為最小值，甚至可防止SQL Server啟動。 如果您在變更此選項之後無法啟動SQL Server，請使用 -f 啟動選項啟動它，並將max server memory (MB) 重設為先前的值。 如需詳細資訊，請參閱 Database Engine Service Startup Options。
• 不建議將 最大伺服器記憶體 (MB) 和 最小伺服器記憶體 (MB) 設為相同值，或接近相同的值。

注意

最大伺服器記憶體選項只會限制SQL Server緩衝集區的大小。 [最大伺服器記憶體] 選項不會限制剩餘的未保留記憶體區域，SQL Server為擴充預存程式、COM 物件、非共用 DLL 和 EXE 等元件配置而離開。

SQL Server可以動態使用記憶體。 不過，您可以手動設定記憶體選項，並限制SQL Server可以存取的記憶體數量。 在設定SQL Server的記憶體數量之前，請先從總實體記憶體、作業系統 (OS) 所需的記憶體總計、不受最大伺服器記憶體 (MB) 設定所控制的記憶體配置，以及任何其他SQL Server實例來判斷適當的記憶體設定 (和其他系統使用時，如果伺服器位於取用記憶體的其他應用程式，包括其他SQL Server) 實例。 此差異是您可以指派給目前SQL Server實例的最大記憶體數量。

記憶體可以設定為所有SQL Server版本中的進程虛擬位址空間限制。 如需詳細資訊，請參閱 Windows 與 Windows Server 版本的記憶體限制。

最小伺服器記憶體

使用最小伺服器記憶體 (MB) 保證SQL Server記憶體管理員可用的記憶體數量下限。

• SQL Server不會在啟動時立即配置最小伺服器記憶體中指定的記憶體數量， (MB) 。 不過，由於用戶端載入而達到此值之後，除非最小伺服器記憶體的值 (MB) ，否則SQL Server無法釋放記憶體。 例如，在同一部伺服器上同時安裝數個SQL Server實例時，請考慮將最小伺服器記憶體 (MB) 參數設定為保留實例的記憶體。

• 在虛擬化環境中設定 最小伺服器記憶體 (MB) 值，以確保基礎主機的記憶體壓力不會嘗試從客體虛擬機器上的緩衝集區解除配置記憶體， (VM) 超出可接受的效能需求。 在理想情況下，虛擬機器中的SQL Server實例不需要與虛擬主機主動式記憶體解除配置程式競爭。

• SQL Server不保證會配置最小伺服器記憶體中指定的記憶體數量， (MB) 。 如果伺服器上的負載絕對不需要配置最小伺服器記憶體中指定的記憶體數量， (MB) ，SQL Server將會使用較少的記憶體。

最大伺服器記憶體

使用最大伺服器記憶體 (MB) ，以確保 OS 和其他應用程式不會遇到來自SQL Server的有害記憶體壓力。

• 在設定最大伺服器記憶體 (MB) 組態之前，請在正常作業期間監視裝載SQL Server實例之伺服器的整體記憶體耗用量，以判斷記憶體可用性和需求。 針對初始設定，或沒有機會收集一段時間SQL Server進程記憶體使用量時，請使用下列一般化最佳做法方法，為單一實例設定最大伺服器記憶體 (MB) ：
  • 從 OS 記憶體總計中，減去最大伺服器記憶體 (MB) 控制項之外潛在SQL Server執行緒記憶體配置的對等專案，也就是堆疊大小¹乘以計算的最大背景工作執行緒²。
  • 然後，針對 最大伺服器記憶體以外的 其他記憶體配置減去 25%， (MB) 控制，例如備份緩衝區、擴充預存程式 DLL、使用自動化程式建立的物件 (sp_OA呼叫) ，以及連結伺服器提供者的配置。 這是泛型近似值，而且您的車程可能會有所不同。
  • 保留的內容應該是單一實例設定的最大 伺服器記憶體 (MB) 設定。

¹ 如需每個架構之執行緒堆疊大小的資訊，請參閱記憶體管理架構指南。

² 如需在目前主機中，為指定數量之親和 CPU 而計算出的預設背景工作執行緒數目相關資訊，請參閱如何設定最大背景工作執行緒伺服器設定選項的文件頁面。

手動設定選項

伺服器選項 最小伺服器記憶體 (MB) 和 最大伺服器記憶體 (MB) 可以設定為跨越記憶體值範圍。 這個方法適用于系統或資料庫管理員，使用其他應用程式或其他在相同主機上執行之SQL Server實例的記憶體需求來設定SQL Server的實例。

使用 Transact-SQL

最小伺服器記憶體 (MB) 和最大伺服器記憶體 (MB) 選項是進階選項。 使用 sp_configure 系統預存程式來變更這些設定時，只有當 顯示進階選項 設定為 1 時，您才能變更這些設定。 這些設定會立即生效，不需要重新啟動伺服器。 如需詳細資訊，請參閱 sp_configure。

下列範例會將 最大伺服器記憶體 (MB) 選項設定為 12，288 MB 或 12 GB。 雖然 sp_configure 將選項的名稱指定為 max server memory (MB) ，但您可以省略 (MB) 。

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 12288;
GO
RECONFIGURE;
GO

下列查詢會傳回目前設定值的相關資訊，以及目前使用中的值。 不論是否 sp_configure 啟用 [顯示進階選項] 選項，此查詢都會傳回結果。

SELECT [name], [value], [value_in_use]
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' OR [name] = 'min server memory (MB)';

使用 SQL Server Management Studio

使用最小伺服器記憶體 (MB) 和最大伺服器記憶體 (MB) ，針對實例SQL Server記憶體 SQL Server管理員所管理的) 重新設定記憶體 (數量。

1. 在物件總管中，請以滑鼠右鍵按一下伺服器，然後選取 [屬性]。

2. 選取 [伺服器屬性] 視窗的 [記憶體] 頁面。 會顯示 [ 最小伺服器記憶體 ] 和 [ 最大伺服器記憶體] 的目前值。

3. 在 [伺服器記憶體選項] 中，輸入 [最小伺服器記憶體 ] 和 [ 最大伺服器記憶體]所需的數位。 如需建議，請參閱本文中的 最小伺服器記憶體 (MB) 和 最大伺服器記憶體 (MB) 。

下列螢幕擷取畫面示範所有三個步驟：

鎖定記憶體中的分頁 (LPIM)

以 Windows 為基礎的應用程式可以使用 Windows 位址視窗延伸模組 (AWE) API，將實體記憶體配置並對應至進程位址空間。 LPIM Windows 原則會決定哪些帳戶可以存取 API 來將資料保留在實體記憶體中，以防止系統將資料分頁至磁片上的虛擬記憶體。 使用 AWE 配置的記憶體會鎖定，直到應用程式明確釋放或結束為止。 在 64 位SQL Server中使用 AWE API 來管理記憶體，通常也稱為鎖定的頁面。 將記憶體分頁到磁碟時，鎖定記憶體分頁可能會讓伺服器保持回應狀態。 當具有執行許可權 sqlservr.exe 的帳戶已獲授與記憶體中的 Windows 鎖定頁面 (LPIM) 使用者權限時，SQL Server Standard版和更新版本的實例會啟用[鎖定記憶體中的分頁] 選項。

若要停用 [鎖定記憶體中的分頁] 選項SQL Server，請移除具有執行 (SQL Server啟動帳戶) 啟動帳戶許可權之帳戶的[鎖定記憶體中的分頁] 使用者權限 sqlservr.exe 。

使用 LPIM 不會影響SQL Server動態記憶體管理，使其能夠在其他記憶體 Clerk 的要求中展開或縮小。 使用 鎖定記憶體中的分頁 使用者權限時，強烈建議您將 最大伺服器記憶體上限設定為 (MB) 。 如需詳細資訊，請參閱 max server memory (MB) 。

當 sqlservr 進程已分頁時，應該使用 LPIM。在此情況下，錯誤記錄檔中會報告錯誤 17890，其類似下列範例：

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

使用 LPIM 與未正確設定的最大伺服器記憶體 (MB) 設定，但不會考慮系統中其他記憶體取用者的設定，可能會因為其他進程所需的記憶體數量而造成不穩定，或SQL Server記憶體需求超出最大伺服器記憶體範圍 (MB) 。 如需詳細資訊，請參閱 最大伺服器記憶體。 如果在 32 位或 64 位) 系統上 (授與 LPIM (LPIM) 許可權的 鎖定分 頁，強烈建議您將 最大伺服器記憶體 (MB) 設為特定值，而不是保留預設值 2，147，483，647 MB (MB) 。

注意

從 SQL Server 2012 (11.x) 開始，Standard Edition 不需要追蹤旗標 845才能使用鎖定的頁面。

啟用 鎖定記憶體中的分頁

在考慮先前的資訊之後，若要將許可權授與服務帳戶SQL Server的服務帳戶，以啟用[鎖定記憶體中的分頁] (選項，請參閱在 Windows) 中啟用鎖定頁面選項。

若要判斷 SQL Server 實例的服務帳戶，請參閱SQL Server 組態管理員或從 sys.dm_server_services 查詢 service_account 。 如需詳細資訊，請參閱 sys.dm_server_services (Transact-SQL) 。

檢視鎖定記憶體狀態中的頁面

若要判斷是否將鎖定記憶體中的分頁許可權授與服務帳戶，以取得SQL Server實例，請使用下列查詢。 SQL Server 2016 (13.x) SP1 和更新版本中支援此查詢。

SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;

的下列值 sql_memory_model_desc 表示 LPIM 的狀態：

• CONVENTIONAL. 不會授與鎖定記憶體許可權中的分頁。
• LOCK_PAGES. 已授與鎖定記憶體許可權中的分頁。
• LARGE_PAGES. 啟用追蹤旗標 834 的企業模式會授與鎖定記憶體中的分頁許可權。 這是進階設定，不建議用於大部分的環境。 如需詳細資訊和重要注意事項，請參閱 追蹤旗標 834。

使用下列方法來判斷SQL Server實例是否使用鎖定的頁面：

• 下列 Transact-SQL 查詢的輸出會指出 的非零值 locked_page_allocations_kb ：

  SELECT osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb 
  FROM sys.dm_os_memory_nodes omn 
  INNER JOIN sys.dm_os_nodes osn ON (omn.memory_node_id = osn.memory_node_id) 
  WHERE osn.node_state_desc <> 'ONLINE DAC';
  

• 目前的SQL Server錯誤記錄檔會在伺服器啟動期間回報「在記憶體管理員中使用鎖定的頁面」訊息。

• DBCC MEMORYSTATUS輸出的 [記憶體管理員] 區段會顯示專案的非零值 AWE Allocated 。

多個SQL Server實例

當您執行多個 Database Engine 實例時，您可以使用不同的方法來管理記憶體：

• 在每個實例中使用 最大伺服器記憶體 (MB) 來控制記憶體使用量， 如上所述。 為每個實例建立最大設定，請注意總額度不超過您電腦上的實體記憶體總數。 您想要提供與執行個體的預期工作負載或資料庫大小成比例的記憶體給每一個執行個體。 此方式的優點是當新的處理序或執行個體啟動時，立刻有記憶體可用。 缺點是，如果您未執行所有實例，則沒有任何執行中的實例能夠利用剩餘的可用記憶體。

• 在每個實例中使用 最小伺服器記憶體 (MB) 來控制記憶體使用量， 如上所述。 建立每個實例的最小設定，讓這些最小值的總和小於您電腦上的實體記憶體總數 1 - 2 GB。 同樣地，您可以建立與該執行個體的預期負載成比例的最小值。 此方式的優點是，若沒有同時執行所有執行個體，則執行的執行個體可使用剩餘可用的記憶體。 當電腦上有另一個耗用大量記憶體的進程時，此方法也很有用，因為它可確保SQL Server至少會取得合理的記憶體數量。 其缺點是，當新的執行個體 (或任何其他處理序) 啟動時，執行中的執行個體可能需要花一些時間來釋出記憶體，尤其如果它們必須將已修改的頁面寫回其資料庫才能這麼做的話。

• 在每個實例中使用 最大伺服器記憶體 (MB) 和 最小伺服器記憶體 (MB) ，以控制記憶體使用量、觀察和調整每個實例的最大使用率，以及各種潛在記憶體使用率層級內的最小記憶體保護。

• 不執行任何動作 (不建議)。 有呈現工作負載的前幾個執行個體傾向於配置所有記憶體。 閒置執行個體或稍後啟動的執行個體最後可能只剩少量的記憶體可用。 SQL Server不會嘗試平衡實例之間的記憶體使用量。 不過，所有執行個體將回應 Windows 記憶體通知訊號，以便調整其記憶體使用量的大小。 Windows 不會在應用程式與記憶體通知 API 之間平衡記憶體。 它只提供對系統上記憶體可用性的全域回應。

您可以變更這些設定，而不必重新啟動執行個體，因此，您可以輕易體驗來尋找使用模式的最佳設定。

範例

A. 將 [最大伺服器記憶體] 選項設定為 4 GB

下列範例會將 max server memory (MB) 選項設定為 4096 MB 或 4 GB。 雖然 sp_configure 將選項的名稱指定為 max server memory (MB) ，但您可以省略 (MB) 。

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

這會輸出類似 Configuration option 'max server memory (MB)' changed from 2147483647 to 4096. Run the RECONFIGURE statement to install. 新記憶體限制的 RECONFIGURE 語句在執行 時立即生效。 如需詳細資訊，請參閱 sp_configure。

B. 判斷目前的記憶體配置

以下查詢會傳回目前配置之記憶體的相關資訊。

SELECT
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
   large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
   locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
   virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
   virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
   virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
   page_fault_count AS sql_page_fault_count,
   memory_utilization_percentage AS sql_memory_utilization_percentage,
   process_physical_memory_low AS sql_process_physical_memory_low,
   process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

C. 檢視 的值 max server memory (MB)

下列查詢會傳回目前設定值和使用中值的相關資訊。 不論是否 sp_configure 啟用 [顯示進階選項] 選項，此查詢都會傳回結果。

SELECT [value], [value_in_use]
FROM sys.configurations WHERE [name] = 'max server memory (MB)';

下一步

• 記憶體管理架構指南
• 效能的監視與微調
• RECONFIGURE (Transact-SQL)
• 伺服器組態選項 (SQL Server)
• sp_configure (Transact-SQL)
• Database Engine 服務啟動選項
• Windows 與 Windows Server 版本的記憶體限制