服务器内存配置选项Server memory configuration options

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

重新配置 SQL ServerSQL Server 实例使用的 SQL Server 进程的内存量(以 MB 为单位)。Reconfigure the amount of memory (in megabytes) for a SQL Server process used by an instance of SQL ServerSQL Server. 有两个服务器内存选项:min server memory 和 max server memory。There are two server memory options, min server memory and max server memory. 这些选项会更改 SQL Server 内存管理器可分配给 SQL Server 进程的内存量。These options change the amount of memory the SQL Server Memory Manager can allocate to a SQL Server process.

这些选项的默认设置和允许的最小值为:The default settings and minimum allowable values for these options are:

选项Option 默认Default 允许的最小值Min allowable
min server memorymin server memory 00 00
max server memorymax server memory 2,147,483,647 兆字节 (MB)2,147,483,647 megabytes (MB) 128 MB128 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.

重要

将“max server memory”值设置得太高可能导致一个 SQL ServerSQL Server 实例与同一主机上承载的其他 SQL ServerSQL Server 实例争用内存。Setting max server memory value too high can cause a single instance of SQL ServerSQL Server 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. 将“max server memory”设置为最小值甚至可能导致无法启动 SQL ServerSQL ServerSetting max server memory to the minimum value can even prevent SQL ServerSQL Server from starting. 如果在更改此选项之后无法启动 SQL ServerSQL Server,请使用“-f”启动选项启动它,并将“max server memory”重置为以前的值 。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 的内存量之前,请通过从总物理内存中减去操作系统所需的内存(即不受 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.

手动设置选项Set options manually

可以将 min server memory 和 max server memory 设置成一个内存范围 。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.

备注

min server memorymax server memory 选项都是高级选项。The min server memory and max server memory options are advanced options. 如果使用 sp_configure 系统存储过程来更改这些设置,则只有在“显示高级选项”设置为 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 不会在启动时立即分配 min server memory 中指定的内存量。will not immediately allocate the amount of memory specified in min server memory on startup. 不过,除非降低 SQL ServerSQL Server min server memory 的值,否则当内存使用量由于客户端负荷而达到该值后, 不能释放内存。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. 此外,为了确保来自基础主机的内存压力不会尝试从来宾 SQL ServerSQL Server 虚拟机 (VM) 上的缓冲池释放超过可接受性能所需的内存,在虚拟环境中设置 min_server_memory 值非常必要。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. 如果服务器上的负荷从不需要分配 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. 若要设置 max server memory 配置,请监视 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 总内存中,为 OS 自身保留 1GB - 4GB。From the total OS memory, reserve 1GB-4GB to the OS itself.
  • 然后,减去等于在“最大服务器内存”控制范围外的潜在 SQL ServerSQL Server 内存分配的值,即堆栈大小1 * 计算出的最大工作线程数 2Then 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. 所得结果就是一个实例设置的 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.

使用 SQL Server Management StudioSQL Server Management StudioUse SQL Server Management StudioSQL Server Management Studio

使用“min server memory”和“max server memory”这两个服务器内存选项重新配置由 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 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. 建议按上述内容设置 max server memory。It is recommended to set a max server memory as detailed above.

下面的屏幕截图演示了所有这三个步骤:The following screenshot demonstrates all three steps:

在 SSMS 中配置内存

锁定内存页 (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. 已向有权运行 sqlservr.exe 的帐户授予 Windows 锁定内存页 (LPIM) 用户权限时,在 SQL ServerSQL Server Standard Edition 和更高版本的实例中将“锁定内存页”选项设置为“打开”。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 动态内存管理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. 使用“锁定内存页”用户权限时,建议按如上所述,为 max server memory 设置一个上限。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: ##%.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: ##%. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,Standard Edition 不需要跟踪标志 845 来使用“锁定页”。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 的多个实例Multiple instances of SQL ServerSQL Server

当运行多个 数据库引擎Database Engine实例时,可以使用三种方法来管理内存:When you are running multiple instances of the 数据库引擎Database Engine, there are three approaches you can use to manage memory:

  • 使用“max server 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.

  • 使用“min server 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.

提供最大内存量Provide the maximum amount of memory

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

示例 A. 将最大服务器内存选项设置为 4 GB。Example A. Set the max server memory option to 4 GB.

以下示例将 max server memory 选项设置为 4 GB。The following example sets the max server memory option to 4 GB. 请注意,虽然 sp_configure 将选项的名称指定为 max server memory (MB),但该示例的演示省略了 (MB)Note that although sp_configure specifies the name of the option as max server memory (MB), the example demonstrates omitting the (MB).

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

此操作输出的语句类似于:This will output a statement similar to:

配置选项“最大服务器内存 (MB)”从 2147483647 更改为 4096。Configuration option 'max server memory (MB)' changed from 2147483647 to 4096. 请运行 RECONFIGURE 语句进行安装。Run the RECONFIGURE statement to install.

示例 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;  

示例 C. 确定“最大服务器内存 (MB)”的值Example C. Determining value for 'max server memory (MB)'

以下查询返回有关当前配置的值和 SQL Server 使用的值的信息。The following query returns information about the currently configured value and the value in use by SQL Server. 无论“显示高级选项”是否为 true,此查询都将返回结果。This query will return results regardless of whether 'show advanced options' is true.

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

后续步骤Next steps

内存管理体系结构指南 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 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