伺服器記憶體組態選項

文章
06/12/2024

適用於：SQL Server

SQL Server 資料庫引擎的記憶體使用率會受到一對設定所限制 (最小伺服器記憶體 (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 啟動選項進行啟動，並將 [最大伺服器記憶體 (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 Memory Manager 有最小記憶體數量可用。

啟動時，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 執行緒記憶體配置的對等項目，也就是堆疊大小¹乘以計算的最大背景工作執行緒²。
- 然後，針對 [最大伺服器記憶體 (MB)] 控制項以外的其他記憶體配置減去 25%，例如備份緩衝區、擴充預存程序 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 Memory Manager 所管理的記憶體數量 (以 MB 為單位)。

在物件總管中，請以滑鼠右鍵按一下伺服器，然後選取 [屬性]。
選取 [伺服器屬性] 視窗的 [記憶體] 頁面。會顯示 [最小伺服器記憶體] 和 [最大伺服器記憶體] 的目前值。
在 [伺服器記憶體選項] 中的 [最小伺服器記憶體] 和 [最大伺服器記憶體] 中，輸入想要的數字。如需建議，請參閱本文中的最小伺服器記憶體 (MB) 和最大伺服器記憶體 (MB)。

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

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

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

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

使用 LPIM 並不會影響 SQL Server 動態記憶體管理，使其可依其他記憶體 Clerk 的要求來擴充或縮減。使用 [在記憶體中鎖定分頁] 使用者權利時，建議設定 [最大伺服器記憶體] 上限。如需詳細資訊，請參閱最大伺服器記憶體 (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) 權限，強烈建議您將 [最大伺服器記憶體 (MB)] 設為特定值，而不是保留預設值 2,147,483,647 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。

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

若要判斷是否將 [鎖定記憶體中的分頁] 權限授與 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 錯誤記錄檔會在伺服器啟動期間報告訊息 Using locked pages in the memory manager 。
DBCC MEMORYSTATUS 輸出的 [記憶體管理員] 區段會顯示 AWE Allocated 項目的非零值。

SQL Server 的多重執行個體

當您執行資料庫引擎的多個執行個體時，有不同的方式可以用來管理記憶體：

使用每個執行個體的 [最大伺服器記憶體 (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)';

分享方式：

伺服器記憶體組態選項

建議

最小伺服器記憶體

最大伺服器記憶體

手動設定選項

使用 Transact-SQL

使用 SQL Server Management Studio

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

啟用 [鎖定記憶體中的分頁]

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

SQL Server 的多重執行個體

範例

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

B. 決定目前的記憶體配置

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

意見反映

更多資源

分享方式：

伺服器記憶體組態選項

建議

最小伺服器記憶體

最大伺服器記憶體

手動設定選項

使用 Transact-SQL

使用 SQL Server Management Studio

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

啟用 [鎖定記憶體中的分頁]

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

SQL Server 的多重執行個體

範例

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

B. 決定目前的記憶體配置

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

相關內容

意見反映

更多資源

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