伺服器記憶體伺服器組態選項Server Memory Server Configuration Options

本主題的適用對象: 是SQL Server沒有Azure SQL Database沒有Azure SQL 資料倉儲沒有Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

您可以使用 [最小伺服器記憶體] 和 [最大伺服器記憶體] 這兩個伺服器記憶體選項,針對 SQL ServerSQL Server 執行個體所使用的 SQL Server 處理序重新設定 SQL Server Memory Manager 所管理的記憶體數量 (以 MB 為單位)。Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) that is managed by the SQL Server Memory Manager for a SQL Server process used by an instance of SQL ServerSQL Server.

[最小伺服器記憶體] 的預設設定為 0,而 [最大伺服器記憶體] 則為 2,147,483,647 MB。The default setting for min server memory is 0, and the default setting for max server memory is 2,147,483,647 megabytes (MB). 根據預設, SQL ServerSQL Server 可以根據可用的系統資源,動態變更其記憶體需求。By default, SQL ServerSQL Server can change its memory requirements dynamically based on available system resources. 如需詳細資訊,請參閱動態記憶體管理For more information, see dynamic memory management.

[最大伺服器記憶體] 允許使用的最小記憶體數量為 128 MB。The minimum memory amount allowable for max server memory is 128 MB.

重要

將 [最大伺服器記憶體] 的值設得太高,可能會導致裝載於相同主機上的單一 SQL ServerSQL Server 執行個體,必須與其他 SQL ServerSQL Server 執行個體爭用記憶體。Setting max server memory value too high can cause a single instance of SQL ServerSQL Server might have to compete for memory with other SQL ServerSQL Server instances hosted on the same host. 但若將此值設得太低,可能也會引起高度的記憶體壓力與效能問題。However, setting this value too low could cause significant memory pressure and performance problems. 將 [最大伺服器記憶體] 設為最小值甚至可能會讓 SQL ServerSQL Server 無法啟動。Setting max server memory to the minimum value can even prevent SQL ServerSQL Server from starting. 如果您變更此選項後無法啟動 SQL ServerSQL Server,請使用 –f 啟動選項啟動它,並將 [最大伺服器記憶體] 重設為先前的值。If you cannot start SQL ServerSQL Server after changing this option, start it using the –f startup option and reset max server memory to its previous value. 如需詳細資訊,請參閱 Database Engine Service Startup OptionsFor more information, see Database Engine Service Startup Options.

SQL ServerSQL Server 可動態使用記憶體。但您可手動設定記憶體選項,並限制 SQL ServerSQL Server 可存取的記憶體數量。 can use memory dynamically; however, you can set the memory options manually and restrict the amount of memory that SQL ServerSQL Server can access. 在您設定 SQL ServerSQL Server 的記憶體數量之前,要先決定適當的記憶體設定,方法是將實體記憶體總數減去 OS、不受 max_server_memory 設定所控制的記憶體配置,以及任何其他 SQL ServerSQL Server 執行個體 (以及當電腦不專用於 SQL ServerSQL Server時,其他的系統用途) 所需要的記憶體。Before you set the amount of memory for SQL ServerSQL Server, determine the appropriate memory setting by subtracting, from the total physical memory, the memory required for the OS, memory allocations not controlled by the max_server_memory setting, and any other instances of SQL ServerSQL Server (and other system uses, if the computer is not wholly dedicated to SQL ServerSQL Server). 此差額即為可指派給目前 SQL ServerSQL Server 執行個體的記憶體數量上限。This difference is the maximum amount of memory you can assign to the current SQL ServerSQL Server instance.

手動設定記憶體選項Setting the memory options manually

您可以將伺服器選項 [最小伺服器記憶體] 與 [最大伺服器記憶體] 設定成跨某範圍的記憶體值。The server options min server memory and max server memory can be set to span a range of memory values. 這樣的做法有助於讓系統或資料庫管理員,將 SQL ServerSQL Server 執行個體配合同一部電腦上執行的其他應用程式或其他 SQL ServerSQL Server 執行個體的記憶體需求,一併設定。This method is useful for system or database administrators to configure an instance of SQL ServerSQL Server in conjunction with the memory requirements of other applications, or other instances of SQL ServerSQL Server that run on the same host.

注意

[最小伺服器記憶體] 和 [最大伺服器記憶體] 選項屬於進階選項。The min server memory and max server memory options are advanced options. 如果您要使用 sp_configure 系統預存程序來變更這些設定,只有當 show advanced options 設為 1 時,才能變更它們。If you are using the sp_configure system stored procedure to change these settings, you can change them only when show advanced options is set to 1. 這些設定會立即生效,不需要重新啟動伺服器。These settings take effect immediately without a server restart.

您可使用 min_server_memory 確保 SQL ServerSQL Server 執行個體的 SQL ServerSQL Server 記憶體管理員,能有最少的記憶體數量可用。Use min_server_memory to guarantee a minimum amount of memory available to the SQL ServerSQL Server Memory Manager for an instance of SQL ServerSQL Server. 啟動時, SQL ServerSQL Server 不會立即配置 [最小伺服器記憶體] 中指定的記憶體數量。 SQL ServerSQL Server will not immediately allocate the amount of memory specified in min server memory on startup. 不過,由於用戶端負載使記憶體使用量達到這個值後,除非降低 [最小伺服器記憶體] 的值,否則 SQL ServerSQL Server 無法釋出記憶體。However, after memory usage has reached this value due to client load, SQL ServerSQL Server cannot free memory unless the value of min server memory is reduced. 例如,當多個 SQL ServerSQL Server 執行個體可以同時存在於相同的主機上時,設定 min_server_memory 參數而非 max_server_memory 可為執行個體保留記憶體。For example, when several instances of SQL ServerSQL Server can exist concurrently in the same host, set the min_server_memory parameter instead of max_server_memory for the purpose of reserving memory for an instance. 此外,也需要設定虛擬環境中的 min_server_memory 值,以確保基礎主機的記憶體壓力不會為了得到可接受的效能,而嘗試從客體 SQL ServerSQL Server 虛擬機器 (VM) 的緩衝集區,解除超過所需的記憶體。Also, setting a min_server_memory value is essential in a virtualized environment to ensure memory pressure from the underlying host does not attempt to deallocate memory from the buffer pool on a guest SQL ServerSQL Server virtual machine (VM) beyond what is needed for acceptable performance.

注意

SQL ServerSQL Server 不保證能夠配置 [最小伺服器記憶體] 中指定的記憶體數量。 is not guaranteed to allocate the amount of memory specified in min server memory. 如果伺服器的負載從不需要配置 [最小伺服器記憶體] 中指定的記憶體大小,則 SQL ServerSQL Server 會以較少的記憶體執行。If the load on the server never requires allocating the amount of memory specified in min server memory, SQL ServerSQL Server will run with less memory.

您可使用 max_server_memory 確保 OS 不會遇到有害的記憶體壓力。Use max_server_memory to guarantee the OS does not experience detrimental memory pressure. 若要設定最大伺服器記憶體設定,請監視 SQL ServerSQL Server 處理序的整體取用量,以判斷記憶體需求。To set max server memory configuration, monitor overall consumption of the SQL ServerSQL Server process in order to determine memory requirements. 若要為單一執行個體進行更準確的計算:To be more accurate with these calculations for a single instance:

  • 從 OS 總記憶體中保留 1GB - 4GB 的記憶體給 OS 本身。From the total OS memory, reserve 1GB-4GB to the OS itself.
  • 然後減去等於不受 [最大伺服器記憶體] 控制的潛在 SQL ServerSQL Server 記憶體配置,該配置的算法為堆疊大小 1 * 計算得出的最大背景工作執行緒數 2 + -g 啟動參數 3 (若未設定 -g,則預設為 256MB)。Then subtract the equivalent of potential SQL ServerSQL Server memory allocations outside the max server memory control, which is comprised of stack size 1 \ calculated max worker threads 2 + -g startup parameter 3* (or 256MB by default if -g is not set). 餘數即為單一執行個體安裝的 max_server_memory 設定。What remains should be the max_server_memory setting for a single instance setup.

1 如需每個架構之執行緒堆疊大小的資訊,請參閱記憶體管理架構指南1 Refer to the Memory Management Architecture guide for information on thread stack sizes per architecture.

2 如需在目前主機中,為指定數量之親和 CPU 而計算出的預設背景工作執行緒數目相關資訊,請參閱如何設定最大背景工作執行緒伺服器設定選項的文件頁面。2 Refer to the documentation page on how to Configure the max worker threads Server Configuration Option, for information on the calculated default worker threads for a given number of affinitized CPUs in the current host.

3 如需 -g 啟動參數的資訊,請參閱資料庫引擎服務啟動選項的文件頁面。3 Refer to the documentation page on Database Engine Service Startup Options for information on the -g startup parameter.

如何使用 Transact-SQLSQL Server Management Studio 設定記憶體選項How to configure memory options using Transact-SQLSQL Server Management Studio

您可使用 [最小伺服器記憶體] 與 [最大伺服器記憶體] 這兩個伺服器記憶體選項,針對 SQL ServerSQL Server 執行個體重新設定 SQL ServerSQL Server 記憶體管理員所管理的記憶體數量 (MB)。Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) managed by the SQL ServerSQL Server Memory Manager for an instance of SQL ServerSQL Server. 根據預設, SQL ServerSQL Server 可以根據可用的系統資源,動態變更其記憶體需求。By default, SQL ServerSQL Server can change its memory requirements dynamically based on available system resources.

若要設定固定的記憶體數量:To set a fixed amount of memory:

  1. 在物件總管中,請以滑鼠右鍵按一下伺服器,然後選取 [屬性]。In Object Explorer, right-click a server and select Properties.

  2. 按一下 [記憶體] 節點。Click the Memory node.

  3. 在 [伺服器記憶體選項] 下,為 [最小伺服器記憶體] 與 [最大伺服器記憶體] 輸入希望的相同數量。Under Server Memory Options, enter the same amount that you want for Minimum server memory and Maximum server memory.

    使用預設值可允許 SQL ServerSQL Server 根據可用的系統資源來動態變更它的記憶體需求。Use the default settings to allow SQL ServerSQL Server to change its memory requirements dynamically based on available system resources. 建議如上詳述,設定 [最大伺服器記憶體]。It is recommended to set a max server memory as detailed above.

鎖定記憶體中的分頁 (LPIM)Lock Pages in Memory (LPIM)

此 Windows 原則決定哪些帳戶可以使用處理序將資料保留在實體記憶體中,以防止系統將資料傳送到磁碟上的虛擬記憶體。This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. 將記憶體分頁到磁碟時,鎖定記憶體分頁可能會讓伺服器保持回應狀態。Locking pages in memory may keep the server responsive when paging memory to disk occurs. SQL ServerSQL Server Standard 版本或更新版本的執行個體中,當具有 sqlservr.exe 執行權限的帳戶取得 Windows「鎖定記憶體中的分頁」 (LPIM) 使用者權利時,[鎖定記憶體中的分頁] 選項會設定為 [開啟]。The Lock Pages in Memory option is set to ON in instances of SQL ServerSQL Server Standard edition and higher when the account with privileges to run sqlservr.exe has been granted the Windows Lock Pages in Memory (LPIM) user right.

若要停用 SQL ServerSQL Server 的 [鎖定記憶體中的分頁] 選項,請將具有 sqlservr.exe ( SQL ServerSQL Server 啟動帳戶) 執行權限之帳戶的 [鎖定記憶體中的分頁] 使用者權利移除。To disable the Lock Pages In Memory option for SQL ServerSQL Server, remove the Lock Pages in Memory user right for the account with privileges to run sqlservr.exe (the SQL ServerSQL Server startup account) startup account.

設定此選項並不會影響 SQL ServerSQL Server 動態記憶體管理,使其可應其他記憶體 Clerk 的要求擴張或縮減。Setting this option does not affect SQL ServerSQL Server dynamic memory management, allowing it to expand or shrink at the request of other memory clerks. 使用 [在記憶體中鎖定分頁] 使用者權利時,建議設定如上詳述的 [最大伺服器記憶體] 上限。When using the Lock Pages in Memory user right it is recommended to set an upper limit for max server memory as detailed above.

重要

請只有必要的情況下才設定此選項,例如出現 sqlservr 程序移出分頁的跡象。在此情況下,錯誤記錄檔中會回報錯誤 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: ##%.SQL Server 2012 (11.x)SQL Server 2012 (11.x) 開始,Standard 版本不需要追蹤旗標 845 即可使用鎖定的分頁。Setting this option should only be used when necessary, namely if there are signs that sqlservr process is being paged out. In this case, error 17890 will be reported in the Errorlog, resembling the below example: 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: ##%. Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), trace flag 845 is not needed for Standard Edition to use Locked Pages.

啟用在記憶體中鎖定分頁To enable Lock Pages in Memory

若要啟用在記憶體中鎖定分頁選項:To enable the lock pages in memory option:

  1. [開始] 功能表上,按一下 [執行]On the Start menu, click Run. 在 [開啟舊檔] 方塊中,輸入 gpedit.mscIn the Open box, type gpedit.msc.

    [群組原則] 對話方塊隨即開啟。The Group Policy dialog box opens.

  2. 在 [群組原則] 主控台上,依序展開 [電腦組態] 和 [Windows 設定]。On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

  3. 依序展開 [安全性設定]和 [本機原則]。Expand Security Settings, and then expand Local Policies.

  4. 選取 [使用者權限指派] 資料夾。Select the User Rights Assignment folder.

    這些原則會顯示在詳細資料窗格中。The policies will be displayed in the details pane.

  5. 在窗格中按兩下 [鎖定記憶體中的分頁]。In the pane, double-click Lock pages in memory.

  6. 在 [本機安全性原則設定] 對話方塊中,新增具有 sqlservr.exe 執行權限的帳戶 ( SQL ServerSQL Server 啟動帳戶)。In the Local Security Policy Setting dialog box, add the account with privileges to run sqlservr.exe (the SQL ServerSQL Server startup account).

執行多個 SQL ServerSQL Server 執行個體Running multiple instances of SQL ServerSQL Server

當您執行 Database EngineDatabase Engine 的多個執行個體時,您有三個方式可以用來管理記憶體:When you are running multiple instances of the Database EngineDatabase Engine, there are three approaches you can use to manage memory:

  • 使用 [最大伺服器記憶體] 來控制記憶體使用量,如上詳述Use max server memory to control memory usage, as detailed above. 建立每一個執行個體的最大設定值,注意,扣除總額不大於機器的總實體記憶體。Establish maximum settings for each instance, being careful that the total allowance is not more than the total physical memory on your machine. 您想要提供與執行個體的預期工作負載或資料庫大小成比例的記憶體給每一個執行個體。You might want to give each instance memory proportional to its expected workload or database size. 此方式的優點是當新的處理序或執行個體啟動時,立刻有記憶體可用。This approach has the advantage that when new processes or instances start up, free memory will be available to them immediately. 缺點是,如果您不是執行所有執行個體,則任何執行中執行個體都不能利用剩餘可用的記憶體。The drawback is that if you are not running all of the instances, none of the running instances will be able to utilize the remaining free memory.

  • 使用 [最小伺服器記憶體] 來控制記憶體使用量,如上詳述Use min server memory to control memory usage, as detailed above. 建立每一個執行個體的最小設定值,使這些最小值的總和小於機器的總實體記憶體 1-2 GB。Establish minimum settings for each instance, so that the sum of these minimums is 1-2 GB less than the total physical memory on your machine. 同樣地,您可以建立與該執行個體的預期負載成比例的最小值。Again, you may establish these minimums proportionately to the expected load of that instance. 此方式的優點是,若沒有同時執行所有執行個體,則執行的執行個體可使用剩餘可用的記憶體。This approach has the advantage that if not all instances are running at the same time, the ones that are running can use the remaining free memory. 當電腦上有另一個記憶體密集處理序時,此方法也很有用,因為它可確保 SQL ServerSQL Server 至少取得合理的記憶體數量。This approach is also useful when there is another memory-intensive process on the computer, since it would insure that SQL ServerSQL Server would at least get a reasonable amount of memory. 其缺點是,當新的執行個體 (或任何其他處理序) 啟動時,執行中的執行個體可能需要花一些時間來釋出記憶體,尤其如果它們必須將已修改的頁面寫回其資料庫才能這麼做的話。The drawback is that when a new instance (or any other process) starts, it may take some time for the running instances to release memory, especially if they must write modified pages back to their databases to do so.

  • 不執行任何動作 (不建議)。Do nothing (not recommended). 有呈現工作負載的前幾個執行個體傾向於配置所有記憶體。The first instances presented with a workload will tend to allocate all of memory. 閒置執行個體或稍後啟動的執行個體最後可能只剩少量的記憶體可用。Idle instances, or instances started later, may end up running with only a minimal amount of memory available. SQL ServerSQL Server 不會嘗試在執行個體之間平衡記憶體使用量。 makes no attempt to balance memory usage across instances. 不過,所有執行個體將回應 Windows 記憶體通知訊號,以便調整其記憶體使用量的大小。All instances will, however, respond to Windows Memory Notification signals to adjust the size of their memory footprint. Windows 不會平衡具有記憶體通知 API 的應用程式之間的記憶體。Windows does not balance memory across applications with the Memory Notification API. 它只提供對系統上記憶體可用性的全域回應。It merely provides global feedback as to the availability of memory on the system.

    您可以變更這些設定,而不必重新啟動執行個體,因此,您可以輕易體驗來尋找使用模式的最佳設定。You can change these settings without restarting the instances, so you can easily experiment to find the best settings for your usage pattern.

為 SQL Server 提供最大的記憶體數量Providing the maximum amount of memory to SQL Server

在所有 SQL ServerSQL Server 版本中,最多可將記憶體設定為處理虛擬位址空間的最大上限。Memory can be configured up to the process virtual address space limit in all SQL ServerSQL Server editions. 如需詳細資訊,請參閱 Windows 與 Windows Server 版本的記憶體限制For more information, see Memory Limits for Windows and Windows Server Releases.

範例Examples

範例 AExample A

下列範例會將 max server memory 選項設定為 4 GB:The following example sets the max server memory option to 4 GB:

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

範例 B:決定目前的記憶體配置Example B. Determining Current Memory Allocation

以下查詢會傳回目前配置之記憶體的相關資訊。The following query returns information about currently allocated memory.

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;  

另請參閱See Also

記憶體管理架構指南 Memory Management Architecture Guide
效能的監視與微調 Monitor and Tune for Performance
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
伺服器組態選項 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
Database Engine 服務啟動選項 Database Engine Service Startup Options
SQL Server 2016 的版本與支援功能 Editions and supported features of SQL Server 2016
SQL Server 2017 的版本與支援功能 Editions and supported features of SQL Server 2017
Linux 上的 SQL Server 2017 版本與支援的功能 Editions and supported features of SQL Server 2017 on Linux
Windows 與 Windows Server 版本的記憶體限制Memory Limits for Windows and Windows Server Releases