服务器内存配置选项

适用于： SQL Server

SQL Server 数据库引擎的内存利用率由一对配置设置进行限制：“最小服务器内存(MB)”和“最大服务器内存(MB)”。 随着时间的推移，在正常情况下，SQL Server 将尝试申请内存，使其达到“最大服务器内存(MB)”设置的限制。

注意

列存储索引：概述和内存中 OLTP 概述和使用方案对象有自己的内存分配器，这样就可以更轻松地监视其缓冲池使用情况。 有关详细信息，请参阅 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)”重置为其之前的值。 有关详细信息，请参阅数据库引擎服务启动选项。
• 建议不要将“最大服务器内存(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 内存管理器使用的最小内存量。

• 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 线程内存分配量的同等值，这个量是堆栈大小¹乘以计算出的最大工作线程数²。
  • 然后减去在“最大服务器内存 (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 实例管理的内存量 (MB)。

1. 在对象资源管理器中，右键单击服务器并选择 “属性” 。

2. 选择“服务器属性”窗口的“内存”页。 这会显示最小服务器内存和最大服务器内存的当前值。

3. 在“服务器内存”选项中，输入最小服务器内存和最大服务器内存所需的数字。 相关建议，请参阅本文中的最小服务器内存 (MB) 和最大服务器内存 (MB)。

下面的屏幕截图演示了所有这三个步骤：

锁定内存页 (LPIM)

基于 Windows 的应用程序可使用 Windows 地址窗口扩展 (AWE) API 来分配物理内存并将其映射到进程地址空间。 LPIM Windows 策略将确定哪些帐户可以访问 API 以将数据保留在物理内存中，从而阻止系统将数据分页到磁盘的虚拟内存中。 使用 AWE 分配的内存被锁定，直到应用程序显式释放该内存或退出。 在 64 位 SQL Server 中使用 AWE API 进行内存管理也经常称为锁定页。 锁定内存中的页可以在发生将内存分页到磁盘时保持服务器的响应能力。 已向有权运行 sqlservr.exe 的帐户授予 Windows 锁定内存页 (LPIM) 用户权限时，SQL Server Standard Edition 及更高版本的实例中已启用“锁定内存页”选项。

若要对 SQL Server 禁用“锁定内存页”选项，请为有权运行 sqlservr.exe（SQL Server 启动帐户）启动帐户的帐户删除“锁定内存页”用户权限。

使用 LPIM 可实现根据其他内存分配器的请求扩大或缩小内存，不影响 SQL Server 动态内存管理。 使用“锁定内存页”用户权限时，强烈建议为最大服务器内存 (MB) 设置上限。 有关详细信息，请参阅最大服务器内存 (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) 设置一起使用可能会导致不稳定，具体取决于其他进程所需的内存量，或超出最大服务器内存 (MB) 范围的 SQL Server 内存要求。 有关详细信息，请参阅最大服务器内存。 如果（在 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)';

相关内容

• 内存管理体系结构指南
• 监视和优化性能
• RECONFIGURE (Transact-SQL)
• 服务器配置选项 (SQL Server)
• sp_configure (Transact-SQL)
• 数据库引擎服务启动选项
• Windows 和 Windows Server 版本的内存限制