SQL Server最大服务器内存如何合理设置？-凌峰创科服务平台

什么是“最大服务器内存”？

“最大服务器内存”（Maximum server memory）是 SQL Server 的一个高级配置选项，它定义了 SQL Server 可以从操作系统请求的最大内存量（以 MB 为单位）。

（图片来源网络，侵删）

它不是一个保证值：它设置的是 SQL Server 内存使用的上限,而不是目标值。
它不限制操作系统：这个限制只针对 SQL Server 进程本身（sqlservr.exe），操作系统和其他应用程序仍然可以使用超过这个限制的内存,前提是系统总内存足够。

为什么需要设置“最大服务器内存”？

如果不设置这个值，SQL Server 会非常“贪婪”，它会尝试使用系统中几乎所有可用的内存，直到达到物理内存的极限,这会导致两个主要问题：

内存压力：当 SQL Server 占用了绝大部分内存后，操作系统和其他应用程序（如杀毒软件、监控工具、业务应用等）会面临严重的内存不足，操作系统被迫将其他程序的内存页交换到硬盘上的页面文件中，这个过程称为换页，硬盘的读写速度比内存慢成千上万倍，会导致整个系统（包括 SQL Server）的性能急剧下降。
操作系统缓存失效：操作系统本身会使用一部分内存作为文件系统缓存（File System Cache），用于缓存磁盘数据，从而加速对文件的读写，SQL Server 占用了所有内存，操作系统的缓存就会失效，使得磁盘 I/O 成为性能瓶颈。

核心原则：为操作系统和其他关键应用程序预留足够的内存,确保整个系统的稳定和高效运行。

如何设置“最大服务器内存”？

设置方法

SQL Server Management Studio (SSMS):
- 连接到你的 SQL Server 实例。
- 右键点击服务器名称，选择“属性”。
- 在左侧导航栏中选择“内存”。
- 在右侧的“服务器内存选项”中，找到“最大服务器内存 (MB)”并输入你希望设置的最大值。
- 点击“确定”保存。
T-SQL 命令:
（图片来源网络，侵删）
- 使用 sp_configure 存储过程。
- ```
-- 显示当前配置
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'max server memory';
GO
```
-- 设置最大内存为 8192 MB (8 GB) -- 注意：设置前最好先检查当前值，避免误操作 EXEC sp_configure 'max server memory', 8192; RECONFIGURE; GO

最佳实践与推荐值

没有一个“放之四海而皆准”的值，最佳配置取决于你的服务器角色和负载,以下是一些通用的指导原则：

通用公式

一个广泛使用的起点公式是：

最大服务器内存 = 总物理内存 - [操作系统缓存 + 其他应用程序内存]

（图片来源网络，侵删）

不同场景下的推荐值

服务器角色	推荐设置	说明
专用 SQL Server (仅运行 SQL Server，无其他关键应用)	总内存的 70% - 80%	这是常见的起点，为操作系统和其他后台进程预留 20%-30% 的内存，对于内存非常大的系统（如 128GB+），可以预留得少一些（10GB-20GB），因为操作系统本身也需要一些内存来管理大文件。
共享服务器 (SQL Server + 其他应用，如 Web 服务器)	总内存的 50% - 70%	需要为其他应用程序预留更多内存，具体比例取决于其他应用的内存需求。
SQL Server 实例	不要设置为 0	设置为 0 意味着不限制，SQL Server 会尽可能多地使用内存，导致系统内存耗尽。
Always On 可用性组	考虑所有副本	AG 的所有副本都在同一台物理机上，你需要为每个副本都预留内存，而不是简单地将总内存除以副本数，因为每个副本都需要自己的工作集。

额外考虑因素

Lock Pages in Memory (LPIM): 如果你为 SQL Server 配置了 Lock Pages in Memory 权限（用于防止操作系统将 SQL Server 内存换出到页面文件），那么为操作系统预留的内存需要更多，因为 SQL Server 的内存将完全不受操作系统管理。
内存密集型工作负载: 如果你的 SQL Server 主要处理大型内存中表、列存储索引或 Power BI 等内存密集型任务，可以适当增加分配给 SQL Server 的内存比例。
动态管理视图 (DMV): 设置后,需要通过监控来验证是否合适。

如何监控和验证？

设置好“最大服务器内存”后，你需要监控 SQL Server 的实际内存使用情况,确保你的设置是合理的。

关键动态管理视图和函数

sys.dm_os_sys_info: 查看总物理内存。

SELECT total_physical_memory_kb / 1024 AS Total_Physical_MB
FROM sys.dm_os_sys_info;

sys.dm_os_process_memory: 查看 SQL Server 进程的内存使用情况。

SELECT 
    physical_memory_in_use_kb / 1024 AS Physical_MB_In_Use,
    virtual_address_space_kb / 1024 AS Virtual_MB_In_Use,
    locked_page_allocations_kb / 1024 AS Locked_MB_Allocated
FROM sys.dm_os_process_memory;

sys.dm_os_performance_counters: 查看内存相关的性能计数器。
- SQL Server:Memory Manager - Target Server Memory (KB): SQL Server 希望使用的内存量（即你设置的 max server memory）。
- SQL Server:Memory Manager - Total Server Memory (KB): SQL Server 当前实际使用的内存量，这个值会接近 Target Server Memory,但不会超过它。
- Memory:Available MBytes: 操作系统当前可用的物理内存，这个值应该保持在一个健康的水平（> 200-500 MB），而不是长期为 0。

监控建议

观察 Total Server Memory: 它应该会稳定地增长，并最终接近你设置的 Target Server Memory。
观察 Available MBytes: 如果这个值长期为 0 或非常低，说明你可能为 SQL Server 分配了太多内存，导致操作系统内存不足，此时应适当降低 max server memory。
观察页面文件: 在任务管理器的“性能”选项卡中，查看“提交”值，已提交”值远大于“提交限制”，说明系统内存严重不足,正在大量使用页面文件。

“最大服务器内存”是 SQL Server 的内存上限，不是目标值。
设置它的主要目的是为操作系统和其他应用程序预留内存，防止系统因内存耗尽而性能崩溃。
对于专用的 SQL Server，一个常见的起点是总内存的 70%-80%。
设置后必须通过 DMV 和性能计数器进行持续监控，并根据实际情况进行调整。
切勿设置为 0，除非你非常清楚你在做什么，并且服务器上没有任何其他应用。

正确配置“最大服务器内存”是 SQL Server 性能调优中最基础、最重要的一步。

SQL Server最大服务器内存如何合理设置？

什么是“最大服务器内存”？

为什么需要设置“最大服务器内存”？

如何设置“最大服务器内存”？

设置方法

最佳实践与推荐值