最大服務器內存選項僅限制 SQL Server 緩沖池的大小。 最大服務器內存選項不限制 SQL Server 為分配其他組件(例如,擴展存儲過程、COM 對象、非共享 DLL 和 EXE)而保留的剩余未預留內存區域。
SQL Server 可動態使用內存。 但是,也可手動設置內存選項并限制 SQL Server 可訪問的內存量。 在設置 SQL Server 的內存量之前,請確定適當的內存設置,方法是從總物理內存中減去操作系統 (OS) 所需的內存(即不受“最大服務器內存(MB)”設置控制的內存分配)和任何其他 SQL Server 實例所需的內存(如果服務器上有其他使用內存的應用程序,包括其他 SQL Server 實例,則還要減去其他系統使用的內存量)。 這個差值就是可以分配給當前 SQL Server 實例使用的最大內存量。
SQL Server 不會在啟動時立即分配在“最小服務器內存(MB)”中指定的內存量。 不過,除非調低“最小服務器內存(MB)”的值,否則當內存使用量由于客戶端負載而達到該值后,SQL Server 不能釋放內存。 例如,在同一臺服務器上同時安裝多個 SQL Server 實例時,請考慮設置“最小服務器內存(MB)”參數,使其為實例預留內存。
為了確保來自基礎主機的內存壓力不會嘗試從來賓虛擬機 (VM) 上的緩沖池釋放超過可接受性能所需的內存,在虛擬環境中設置“最小服務器內存(MB)”值非常有必要。 理想情況下,虛擬機中的 SQL Server 實例不必與虛擬主機主動內存解除分配進程競爭。
SQL Server 并不一定分配“最小服務器內存(MB)”中指定的內存量。 如果服務器上的負載從不需要分配“最小服務器內存(MB)”中指定的內存量,則 SQL Server 將使用更少的內存。
最大服務器內存
使用“最大服務器內存(MB)”保證 OS 和其他應用程序不會遇到來自 SQL Server 的不利內存壓力。
在設置“最大服務器內存(MB)”配置之前,在正常操作期間監視托管 SQL Server 實例的服務器的總體內存消耗,以確定內存可用性和要求。 對于初始配置,或者當沒有機會收集一段時間內的 SQL Server 進程內存使用情況時,請使用以下通用最佳做法方法,為單個實例配置最大服務器內存 (MB):
從總 OS 內存中減去“最大服務器內存(MB)”控制之外的潛在 SQL Server 線程內存分配量的同等值,這個量是堆棧大小1乘以計算出的最大工作線程數2。
基于 Windows 的應用程序可使用 Windows 地址窗口擴展 (AWE) API 來分配物理內存并將其映射到進程地址空間。 LPIM Windows 策略將確定哪些帳戶可以訪問 API 以將數據保留在物理內存中,從而阻止系統將數據分頁到磁盤的虛擬內存中。 使用 AWE 分配的內存被鎖定,直到應用程序顯式釋放該內存或退出。 在 64 位 SQL Server 中使用 AWE API 進行內存管理也經常稱為鎖定頁。 鎖定內存中的頁可以在發生將內存分頁到磁盤時保持服務器的響應能力。 已向有權運行 的帳戶授予 Windows 鎖定內存頁 (LPIM) 用戶權限時,SQL Server Standard Edition 及更高版本的實例中已啟用“鎖定內存頁”選項。
若要對 SQL Server 禁用“鎖定內存頁”選項,請為有權運行 (SQL Server 啟動帳戶)啟動帳戶的帳戶刪除“鎖定內存頁”用戶權限。
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: ##%.
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
INNERJOIN sys.dm_os_nodes AS 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 報告消息。
不執行任何操作(不推薦)。 帶有工作負載的第一個實例通常分配所有的內存。 空閑實例或稍后啟動的實例最終可能會只使用最少的可用內存量運行。 SQL Server 不會嘗試均衡分配各個實例的內存使用量。 但是,所有實例均將響應 Windows 內存通知信號以調整它們內存需求量的大小。 Windows 不會使用內存通知 API 來平衡各個應用程序使用的內存。 它只提供有關系統內存可用性的全局反饋。
您可以在不重新啟動實例的情況下更改這些設置,以便可以輕松地進行嘗試以找到適合使用模式的最佳設置。
示例
答: 將最大服務器內存選項設置為 4 GB
以下示例將“最大服務器內存(MB)”選項設置為 4096 MB (4 GB)。 雖然 sp_configure 將選項的名稱指定為 max server memory (MB),但你也可省略 (MB)。
SQL
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. 的語句。新的內存限制在執行 RECONFIGURE 時立即生效。 有關詳細信息,請參閱 sp_configure。
B. 確定當前內存分配
以下查詢返回有關當前分配內存的信息。
SQL
SELECT physical_memory_in_use_kb / 1024AS sql_physical_memory_in_use_MB,
large_page_allocations_kb / 1024AS sql_large_page_allocations_MB,
locked_page_allocations_kb / 1024AS sql_locked_page_allocations_MB,
virtual_address_space_reserved_kb / 1024AS sql_VAS_reserved_MB,
virtual_address_space_committed_kb / 1024AS sql_VAS_committed_MB,
virtual_address_space_available_kb / 1024AS 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;