服務(wù)器內(nèi)存配置選項

適用于：SQL Server

SQL Server 數(shù)據(jù)庫引擎的內(nèi)存利用率由一對配置設(shè)置進(jìn)行限制：“最小服務(wù)器內(nèi)存(MB)”和“最大服務(wù)器內(nèi)存(MB)”。 隨著時間的推移，在正常情況下，SQL Server 將嘗試申請內(nèi)存，使其達(dá)到“最大服務(wù)器內(nèi)存(MB)”設(shè)置的限制。

在較舊版本的 SQL Server 中，內(nèi)存利用率幾乎未設(shè)上限，這向 SQL Server 指示所有系統(tǒng)內(nèi)存均可供使用。 建議在所有版本的 SQL Server 中配置最大服務(wù)器內(nèi)存 (MB)來配置 SQL Server 內(nèi)存利用率上限。

• 由于 SQL Server 2019 (15.x)，Windows 服務(wù)器中的 SQL 安裝程序根據(jù)安裝時可用系統(tǒng)內(nèi)存的百分比，為獨(dú)立 SQL Server 實例提供“最大服務(wù)器內(nèi)存(MB)”的建議。
• 隨時可通過“最小服務(wù)器內(nèi)存(MB)”和“最大服務(wù)器內(nèi)存(MB)”配置選項，為 SQL Server 實例使用的 SQL Server 進(jìn)程重新配置內(nèi)存邊界 (MB)。

這些選項的默認(rèn)設(shè)置和允許的最小值為：

在這些邊界內(nèi)，SQL Server 可根據(jù)可用系統(tǒng)資源動態(tài)更改其內(nèi)存要求。 有關(guān)詳細(xì)信息，請參閱動態(tài)內(nèi)存管理。

• 將“最大服務(wù)器內(nèi)存(MB)”值設(shè)置得太高可能導(dǎo)致一個 SQL Server 實例與同一主機(jī)上承載的其他 SQL Server 實例爭用內(nèi)存。
• 但是，將“最大服務(wù)器內(nèi)存(MB)”設(shè)置得太低會降低性能，可能會導(dǎo)致 SQL Server 實例中出現(xiàn)內(nèi)存壓力和性能問題。
• 將“最大服務(wù)器內(nèi)存(MB)”設(shè)置為最小值甚至可能導(dǎo)致無法啟動 SQL Server。 如果在更改此選項后無法啟動 SQL Server，請使用 -f 啟動選項來啟動它，并將“最大服務(wù)器內(nèi)存(MB)”重置為其之前的值。 有關(guān)詳細(xì)信息，請參閱數(shù)據(jù)庫引擎服務(wù)啟動選項。
• 建議不要將“最大服務(wù)器內(nèi)存(MB)”和“最小服務(wù)器內(nèi)存(MB)”設(shè)置為相同值或接近相同的值。

SQL Server 可動態(tài)使用內(nèi)存。 但是，也可手動設(shè)置內(nèi)存選項并限制 SQL Server 可訪問的內(nèi)存量。 在設(shè)置 SQL Server 的內(nèi)存量之前，請確定適當(dāng)?shù)膬?nèi)存設(shè)置，方法是從總物理內(nèi)存中減去操作系統(tǒng) (OS) 所需的內(nèi)存（即不受“最大服務(wù)器內(nèi)存(MB)”設(shè)置控制的內(nèi)存分配）和任何其他 SQL Server 實例所需的內(nèi)存（如果服務(wù)器上有其他使用內(nèi)存的應(yīng)用程序，包括其他 SQL Server 實例，則還要減去其他系統(tǒng)使用的內(nèi)存量）。 這個差值就是可以分配給當(dāng)前 SQL Server 實例使用的最大內(nèi)存量。

在 SQL Server 的所有版本中，內(nèi)存最大可配置為進(jìn)程虛擬地址空間限制。 有關(guān)詳細(xì)信息，請參閱 Windows 和 Windows Server 版本的內(nèi)存限制。

使用“最小服務(wù)器內(nèi)存(MB)”可保證可供 SQL Server 內(nèi)存管理器使用的最小內(nèi)存量。

• SQL Server 不會在啟動時立即分配在“最小服務(wù)器內(nèi)存(MB)”中指定的內(nèi)存量。 不過，除非調(diào)低“最小服務(wù)器內(nèi)存(MB)”的值，否則當(dāng)內(nèi)存使用量由于客戶端負(fù)載而達(dá)到該值后，SQL Server 不能釋放內(nèi)存。 例如，在同一臺服務(wù)器上同時安裝多個 SQL Server 實例時，請考慮設(shè)置“最小服務(wù)器內(nèi)存(MB)”參數(shù)，使其為實例預(yù)留內(nèi)存。

• 為了確保來自基礎(chǔ)主機(jī)的內(nèi)存壓力不會嘗試從來賓虛擬機(jī) (VM) 上的緩沖池釋放超過可接受性能所需的內(nèi)存，在虛擬環(huán)境中設(shè)置“最小服務(wù)器內(nèi)存(MB)”值非常有必要。 理想情況下，虛擬機(jī)中的 SQL Server 實例不必與虛擬主機(jī)主動內(nèi)存解除分配進(jìn)程競爭。

• SQL Server 并不一定分配“最小服務(wù)器內(nèi)存(MB)”中指定的內(nèi)存量。 如果服務(wù)器上的負(fù)載從不需要分配“最小服務(wù)器內(nèi)存(MB)”中指定的內(nèi)存量，則 SQL Server 將使用更少的內(nèi)存。

使用“最大服務(wù)器內(nèi)存(MB)”保證 OS 和其他應(yīng)用程序不會遇到來自 SQL Server 的不利內(nèi)存壓力。

• 在設(shè)置“最大服務(wù)器內(nèi)存(MB)”配置之前，在正常操作期間監(jiān)視托管 SQL Server 實例的服務(wù)器的總體內(nèi)存消耗，以確定內(nèi)存可用性和要求。 對于初始配置，或者當(dāng)沒有機(jī)會收集一段時間內(nèi)的 SQL Server 進(jìn)程內(nèi)存使用情況時，請使用以下通用最佳做法方法，為單個實例配置最大服務(wù)器內(nèi)存 (MB)：
  • 從總 OS 內(nèi)存中減去“最大服務(wù)器內(nèi)存(MB)”控制之外的潛在 SQL Server 線程內(nèi)存分配量的同等值，這個量是堆棧大小1乘以計算出的最大工作線程數(shù)2。
  • 然后減去在“最大服務(wù)器內(nèi)存 (MB)”控制范圍外的其他內(nèi)存分配的 25%，例如備份緩沖區(qū)、擴(kuò)展存儲過程 DLL、使用自動化過程（sp_OA 調(diào)用）創(chuàng)建的對象以及來自鏈接服務(wù)器提供程序的分配。 這是一個通用近似值，實際情況可能會有所不同。
  • 對于單個實例設(shè)置，剩下的應(yīng)該就是“最大服務(wù)器內(nèi)存(MB)”設(shè)置。

1 有關(guān)每個體系結(jié)構(gòu)的線程堆棧大小的信息，請參閱內(nèi)存管理體系結(jié)構(gòu)指南。

2 有關(guān)當(dāng)前主機(jī)中給定數(shù)量的綁定 CPU 的默認(rèn)工作線程計算詳細(xì)信息，請參閱 服務(wù)器配置：最大工作線程。

可將“最小服務(wù)器內(nèi)存(MB)”和“最大服務(wù)器內(nèi)存(MB)”設(shè)置為跨一系列內(nèi)存值。 在需要兼顧同一臺主機(jī)上運(yùn)行的其他應(yīng)用程序或其他 SQL Server 實例的內(nèi)存要求時，此方法對于配置 SQL Server 實例的系統(tǒng)或數(shù)據(jù)庫管理員來說非常有用。

“最小服務(wù)器內(nèi)存(MB)”和“最大服務(wù)器內(nèi)存(MB)”選項都是高級選項。 使用 sp_configure 系統(tǒng)存儲過程來更改這些設(shè)置時，只能在“顯示高級選項”設(shè)置為 1 時才能更改它們。 這些設(shè)置更改后會立即生效，不需要重新啟動服務(wù)器。 有關(guān)詳細(xì)信息，請參閱 sp_configure。

以下示例將“最大服務(wù)器內(nèi)存(MB)”選項設(shè)置為 12,288 MB (12 GB)。 雖然 sp_configure 將選項的名稱指定為 max server memory (MB)，但你也可省略 (MB)。

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

以下查詢返回當(dāng)前配置的值和當(dāng)前使用的值的相關(guān)信息。 無論是否已啟用“sp_configure”選項“顯示高級選項”，此查詢都將返回結(jié)果。

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

使用“最小服務(wù)器內(nèi)存(MB)”和“最大服務(wù)器內(nèi)存(MB)”重新配置由 SQL Server 內(nèi)存管理器為 SQL Server 實例管理的內(nèi)存量 (MB)。

1. 在對象資源管理器中，右鍵單擊服務(wù)器并選擇 “屬性” 。

2. 選擇“服務(wù)器屬性”窗口的“內(nèi)存”頁。 這會顯示最小服務(wù)器內(nèi)存和最大服務(wù)器內(nèi)存的當(dāng)前值。

3. 在“服務(wù)器內(nèi)存”選項中，輸入最小服務(wù)器內(nèi)存和最大服務(wù)器內(nèi)存所需的數(shù)字。 相關(guān)建議，請參閱本文中的最小服務(wù)器內(nèi)存 (MB) 和最大服務(wù)器內(nèi)存 (MB)。

下面的屏幕截圖演示了所有這三個步驟：

基于 Windows 的應(yīng)用程序可使用 Windows 地址窗口擴(kuò)展 (AWE) API 來分配物理內(nèi)存并將其映射到進(jìn)程地址空間。 LPIM Windows 策略將確定哪些帳戶可以訪問 API 以將數(shù)據(jù)保留在物理內(nèi)存中，從而阻止系統(tǒng)將數(shù)據(jù)分頁到磁盤的虛擬內(nèi)存中。 使用 AWE 分配的內(nèi)存被鎖定，直到應(yīng)用程序顯式釋放該內(nèi)存或退出。 在 64 位 SQL Server 中使用 AWE API 進(jìn)行內(nèi)存管理也經(jīng)常稱為鎖定頁。 鎖定內(nèi)存中的頁可以在發(fā)生將內(nèi)存分頁到磁盤時保持服務(wù)器的響應(yīng)能力。 已向有權(quán)運(yùn)行 的帳戶授予 Windows 鎖定內(nèi)存頁 (LPIM) 用戶權(quán)限時，SQL Server Standard Edition 及更高版本的實例中已啟用“鎖定內(nèi)存頁”選項。

若要對 SQL Server 禁用“鎖定內(nèi)存頁”選項，請為有權(quán)運(yùn)行 （SQL Server 啟動帳戶）啟動帳戶的帳戶刪除“鎖定內(nèi)存頁”用戶權(quán)限。

使用 LPIM 可實現(xiàn)根據(jù)其他內(nèi)存分配器的請求擴(kuò)大或縮小內(nèi)存，不影響 SQL Server 動態(tài)內(nèi)存管理。 使用“鎖定內(nèi)存頁”用戶權(quán)限時，強(qiáng)烈建議為最大服務(wù)器內(nèi)存 (MB) 設(shè)置上限。 有關(guān)詳細(xì)信息，請參閱最大服務(wù)器內(nèi)存 (MB)。

僅應(yīng)在有跡象表明正在換出 sqlservr 進(jìn)程時使用 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 與未考慮系統(tǒng)中其他內(nèi)存消耗者的錯誤配置的最大服務(wù)器內(nèi)存 (MB) 設(shè)置一起使用可能會導(dǎo)致不穩(wěn)定，具體取決于其他進(jìn)程所需的內(nèi)存量，或超出最大服務(wù)器內(nèi)存 (MB) 范圍的 SQL Server 內(nèi)存要求。 有關(guān)詳細(xì)信息，請參閱最大服務(wù)器內(nèi)存。 如果（在 32 位或 64 位系統(tǒng)上）授予了“鎖定內(nèi)存頁”(LPIM) 權(quán)限，則強(qiáng)烈建議將“最大服務(wù)器內(nèi)存(MB)”設(shè)置為特定值，而不是將默認(rèn)值保留為 2,147,483,647 MB。

考慮上述信息后，若要通過向 SQL Server 實例的服務(wù)帳戶授予權(quán)限來啟用“鎖定內(nèi)存頁”選項，請參閱啟用“鎖定內(nèi)存頁”選項 (Windows)。

若要確定 SQL Server 實例的服務(wù)帳戶，請參閱 SQL Server 配置管理器或在 service_account 中查詢 sys.dm_server_services。 有關(guān)詳細(xì)信息，請參閱 sys.dm_server_services。

若要確定是否向 SQL Server 實例的服務(wù)帳戶授予“鎖定內(nèi)存頁”權(quán)限，請使用以下查詢。 SQL Server 2016 (13.x) SP1 及更高版本支持此查詢。

SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;

sql_memory_model_desc 的以下值指示 LPIM 的狀態(tài)：

• CONVENTIONAL。 未授予“鎖定內(nèi)存頁”權(quán)限。
• LOCK_PAGES。 已授予“鎖定內(nèi)存頁”權(quán)限。
• LARGE_PAGES。 已在啟用了跟蹤標(biāo)志 834 的企業(yè)模式中授予“鎖定內(nèi)存頁”權(quán)限。 這是一種高級配置，不建議對大多數(shù)環(huán)境使用。 有關(guān)詳細(xì)信息和重要的注意事項，請參閱跟蹤標(biāo)志 834。

使用以下方法確定 SQL Server 實例是否正在使用鎖定頁：

• 以下 Transact-SQL 查詢的輸出指示了 locked_page_allocations_kb 的非零值：

  SQL復(fù)制

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

• 當(dāng)前 SQL Server 錯誤日志在服務(wù)器啟動期間 Using locked pages in the memory manager 報告消息。

• DBCC MEMORYSTATUS 輸出的內(nèi)存管理器部分將顯示 AWE Allocated 項的非零值。

當(dāng)運(yùn)行多個數(shù)據(jù)庫引擎實例時，可使用不同方法來管理內(nèi)存：

• 在每個實例中使用最大服務(wù)器內(nèi)存 (MB) 來控制內(nèi)存使用量，如上所述。 為每個實例建立最大設(shè)置，注意總的允許設(shè)置值不能大于計算機(jī)上的物理總內(nèi)存。 可能需要為每個實例提供與預(yù)期的工作負(fù)荷或數(shù)據(jù)庫大小成正比的內(nèi)存。 這種方法的優(yōu)勢體現(xiàn)在：當(dāng)啟動新的進(jìn)程或?qū)嵗龝r，可以立即為這些進(jìn)程或?qū)嵗峁┛捎脙?nèi)存。 這種方法的缺點為：如果沒有運(yùn)行所有實例，則所有運(yùn)行中的實例都無法使用剩余的可用內(nèi)存。

• 在每個實例中使用最小服務(wù)器內(nèi)存 (MB) 來控制內(nèi)存使用量，如上所述。 為每個實例建立最小設(shè)置，以使這些最小值的和比計算機(jī)上總的物理內(nèi)存小 1-2 GB。 此外，可能需要建立與該實例的預(yù)期負(fù)荷成正比的最小值。 這種方法的優(yōu)勢體現(xiàn)在：如果沒有同時運(yùn)行所有實例，則運(yùn)行中的實例可以使用剩余的可用內(nèi)存。 當(dāng)計算機(jī)上存在其他占用大量內(nèi)存的進(jìn)程時，這種方法也十分有用，因為它可確保 SQL Server 至少獲得合理的內(nèi)存量。 這種方法的缺點是：當(dāng)啟動新的實例（或任何其他進(jìn)程）時，運(yùn)行的實例可能會花費(fèi)一些時間來釋放內(nèi)存，如果實例必須將修改后的頁寫回到數(shù)據(jù)庫中來釋放內(nèi)存，則花費(fèi)的時間可能會更長。

• 在每個實例中使用“最大服務(wù)器內(nèi)存(MB)”和“最小服務(wù)器內(nèi)存(MB)”來控制內(nèi)存使用量，從而在廣泛的潛在內(nèi)存利用率級別觀察和優(yōu)化每個實例的最大利用率和最小內(nèi)存保護(hù)范圍。

• 不執(zhí)行任何操作（不推薦）。 帶有工作負(fù)載的第一個實例通常分配所有的內(nèi)存。 空閑實例或稍后啟動的實例最終可能會只使用最少的可用內(nèi)存量運(yùn)行。 SQL Server 不會嘗試均衡分配各個實例的內(nèi)存使用量。 但是，所有實例均將響應(yīng) Windows 內(nèi)存通知信號以調(diào)整它們內(nèi)存需求量的大小。 Windows 不會使用內(nèi)存通知 API 來平衡各個應(yīng)用程序使用的內(nèi)存。 它只提供有關(guān)系統(tǒng)內(nèi)存可用性的全局反饋。

您可以在不重新啟動實例的情況下更改這些設(shè)置，以便可以輕松地進(jìn)行嘗試以找到適合使用模式的最佳設(shè)置。

以下示例將“最大服務(wù)器內(nèi)存(MB)”選項設(shè)置為 4096 MB (4 GB)。 雖然 sp_configure 將選項的名稱指定為 max server memory (MB)，但你也可省略 (MB)。

EXECUTE sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXECUTE 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. 的語句。新的內(nèi)存限制在執(zhí)行 RECONFIGURE 時立即生效。 有關(guān)詳細(xì)信息，請參閱 sp_configure。

以下查詢返回有關(guān)當(dāng)前分配內(nèi)存的信息。

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;

以下查詢返回當(dāng)前配置的值和正在使用的值的相關(guān)信息。 無論是否已啟用“sp_configure”選項“顯示高級選項”，此查詢都將返回結(jié)果。

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