伺服器記憶體組態選項

適用于：SQL Server (所有支援的版本)

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

注意

資料行存放區索引 和 記憶體內部 OLTP 不會使用 Database Engine 配置的記憶體。

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

• 由於 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% 的可用系統記憶體，包括記憶體內部 OLTP、資料行存放區索引 和其他實例。 如需更詳細的建議，請參閱 最大伺服器記憶體。

在這些界限內，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 可以存取的記憶體數量。 在設定 SQL Server 的記憶體數量之前，請先從總實體記憶體、作業系統 (OS) 所需的記憶體、不受最大 伺服器記憶體 (MB) 設定所控制的記憶體配置，以及 SQL Server (和其他系統使用的任何其他實例，來判斷適當的記憶體設定。 如果伺服器是取用記憶體的其他應用程式，則為 ，包括其他 SQL Server 實例) 。 此差異是您可以指派給目前 SQL Server 實例的最大記憶體數量。

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

最小伺服器記憶體

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

• SQL Server 不會在啟動時立即配置最小 伺服器記憶體中指定的記憶體數量， (MB) 。 不過，由於用戶端負載導致記憶體使用量達到此值之後，除非 減少最小伺服器記憶體的值， 否則 SQL Server 無法釋放記憶體 (MB) 。 例如，在同一部伺服器上同時安裝數個 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 原則會決定哪些帳戶可以存取 API 以將資料保留在實體記憶體中，以防止系統將資料分頁至磁片上的虛擬記憶體。 將記憶體分頁到磁碟時，鎖定記憶體分頁可能會讓伺服器保持回應狀態。 當具有執行許可權 sqlservr.exe 的帳戶已獲授與記憶體中的 Windows 鎖定頁面， (LPIM) 使用者權限時，就會在 SQL Server Standard Edition 和更新版本中啟用[鎖定記憶體中的分頁] 選項。

若要停用 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 實例

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

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

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

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

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

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

範例

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

下列範例會將 最大伺服器記憶體 (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 版本的記憶體限制