SQLServer的内存管理架构详解（sql server内存设置多少合理）硬核推荐

地址空间的已提交区域由 Windows 虚拟内存管理器 （VMM） 映射到可用的物理内存。

虚拟内存系统允许物理内存的过度使用，因此虚拟内存与物理内存的比率可以超过 1：1。因此，较大的程序可以在具有各种物理内存配置的计算机上运行。但是，使用比所有进程的总平均工作集多得多的虚拟内存可能会导致性能不佳。

SQL Server 根据需要动态获取和释放内存。通常，管理员不必指定应分配给 SQL Server 的内存量，尽管该选项仍然存在并且在某些环境中是必需的。

所有数据库软件的主要设计目标之一是最小化磁盘 I/O，因为磁盘读取和写入是最耗费资源的操作之一。SQL Server 在内存中构建一个缓冲池，以保存从数据库中读取的页。SQL Server 中的许多代码专用于最大程度地减少磁盘和缓冲池之间的物理读取和写入次数。SQL Server 试图在两个目标之间取得平衡：

防止缓冲池变得太大，以致整个系统内存不足。通过最大化缓冲池的大小，最大限度地减少数据库文件的物理 I/O。

在负载较重的系统中，某些需要大量内存才能运行的大型查询无法获取请求的最小内存量，并在等待内存资源时收到超时错误。若要解决此问题，请增加查询等待选项。对于并行查询，请考虑降低最大并行度选项。

在内存压力很大的系统中，当查询未获得位图所需的最小内存时，查询计划中具有合并联接、排序和位图的查询可能会删除位图。这可能会影响查询性能，如果排序过程无法容纳在内存中，则可能会增加数据库中工作表的使用量，从而导致增长。若要解决此问题，请添加物理内存，或调整查询以使用不同的更快查询计划。

所有 SQL Server 版本都支持 64 位平台上的传统内存。SQL Server 进程可以访问虚拟地址空间，最高可达 x64 体系结构上的操作系统最大值（SQL Server 标准版最多支持 128 GB）。对于 IA64 体系结构，限制为 7 TB（SQL Server 64 （2012.x） 及更高版本不支持 IA11）。

通过使用地址窗口化扩展插件 （AWE） 和 AWE 所需的内存中锁定页 （LPIM） 特权，可以在低虚拟内存条件下将大部分 SQL Server 进程内存锁定在物理 RAM 中。这在 32 位和 64 位 AWE 分配中都会发生。发生内存锁定的原因是 AWE 内存不通过 Windows 中的虚拟内存管理器，该管理器控制内存分页。AWE 内存分配 API 需要“锁定内存中的页”（SeLockMemoryPrivilege） 权限。因此，使用 AWE API 的主要好处是在系统上存在内存压力时将大部分内存驻留在 RAM 中。

如果授予 LPIM，我们强烈建议您将最大服务器内存 （MB） 设置为特定值，而不是保留默认值 2，147，483，647 兆字节 （MB）。

如果未启用 LPIM，SQL Server 将切换到使用常规内存，并且在操作系统内存耗尽的情况下，错误日志中可能会报告错误 17890。

从 SQL Server 2012 （11.x） 开始，单页分配、多页分配和 CLR 分配都合并到“任何大小”页面分配器中，并包含在由最大服务器内存 （MB） 和最小服务器内存 （MB） 配置选项控制的内存限制中。此更改为通过 SQL Server 内存管理器的所有内存要求提供了更准确的大小调整功能。

从 SQL Server 2012 （11.x） 开始，SQL Server 分配的内存可能会超过最大服务器内存 （MB） 设置中指定的值。当总服务器内存 （KB） 值已达到目标服务器内存 （KB） 设置时，可能会发生此行为，如最大服务器内存 （MB） 指定。如果由于内存碎片而没有足够的连续可用内存来满足多页内存请求（超过 8 KB）的需求，则 SQL Server 可以执行过度使用，而不是拒绝内存请求。

执行此分配后，资源监视器后台任务将开始向所有内存使用者发出释放分配的内存的信号，并尝试使总服务器内存 （KB） 值低于目标服务器内存 （KB） 规范。因此，SQL Server 内存使用情况可能会短暂超过最大服务器内存 （MB） 设置。在此情况下，总服务器内存 （KB） 性能计数器读数将超过最大服务器内存 （MB） 和目标服务器内存 （KB） 设置。

在较旧版本的 SQL Server 中，SQL Server 内存管理器留出一部分进程虚拟地址空间 （VAS） 供多页分配器 （MPA）、CLR 分配器、SQL Server 进程中线程堆栈的内存分配和直接窗口分配 （DWA） 使用。虚拟地址空间的这一部分也称为“内存要离开”或“非缓冲池”区域。

为这些分配保留的虚拟地址空间由memory_to_reserve配置选项确定。SQL Server 使用的默认值为 256 MB。

由于“任何大小”页面分配器还处理大于 8 KB 的分配，因此memory_to_reserve值不包括多页分配。除此更改外，此配置选项的其他所有内容都保持不变。

SQL Server 数据库引擎的默认内存管理行为是根据需要获取尽可能多的内存，而不会在系统上造成内存不足。SQL Server 数据库引擎通过使用 Microsoft Windows 中的内存通知 API 来执行此操作。

当 SQL Server 动态使用内存时，它会定期查询系统以确定可用内存量。维护此可用内存可防止操作系统 （OS） 分页。如果可用内存较少，SQL Server 将向操作系统释放内存。如果有更多内存可用，SQL Server 可能会分配更多内存。SQL Server 仅在其工作负荷需要更多内存时才添加内存;静态服务器不会增加其虚拟地址空间的大小。如果您注意到任务管理器和性能监视器在使用动态内存管理时显示可用内存稳定减少，这是默认行为，不应被视为内存泄漏。

最大服务器内存控制 SQL Server 内存分配、编译内存、所有缓存（包括缓冲池）、查询执行内存授予、锁管理器内存和 CLR1记忆（基本上是在sys.dm_os_memory_clerks中找到的任何记忆职员）。

CLR 内存在从 SQL Server 2012 （11.x） 开始的max_server_memory分配下进行管理。

示例：查询返回有关当前分配的内存的信息。

线程堆栈的内存、CLR、文件.dll扩展过程、分布式查询引用的 OLE DB 提供程序、Transact-SQL 语句中引用的自动化对象以及非 SQL Server DLL 分配的任何内存不受最大服务器内存 （MB） 的控制。

当 SQL Server 启动时，它会根据多个参数（如系统上的物理内存量、服务器线程数和各种启动参数）计算缓冲池的虚拟地址空间的大小。SQL Server 为缓冲池保留其进程虚拟地址空间的计算量，但它仅获取（提交）当前加载所需的物理内存量。

然后，实例会根据需要继续获取内存以支持工作负载。随着越来越多的用户连接和运行查询，SQL Server 会按需获取更多的物理内存。SQL Server 实例继续获取物理内存，直到达到其最大服务器内存 （MB） 分配目标或操作系统指示不再有多余的可用内存;当内存超过最小服务器内存设置时，它会释放内存，并且操作系统指示可用内存不足。

当其他应用程序在运行 SQL Server 实例的计算机上启动时，它们会消耗内存，并且可用物理内存量将低于 SQL Server 目标。SQL Server 实例调整其内存消耗。如果另一个应用程序停止并且有更多内存可用，则 SQL Server 实例会增加其内存分配的大小。SQL Server 每秒可以释放和获取几兆字节的内存，从而能够快速适应内存分配更改。

SQL Server 数据库的主要用途是存储和检索数据，因此密集磁盘 I/O 是数据库引擎的核心特征。由于磁盘 I/O 操作可能会消耗许多资源并且需要相对较长的时间才能完成，因此 SQL Server 专注于提高 I/O 的效率。缓冲液管理是实现这种效率的关键组成部分。缓冲区管理组件由两种机制组成：用于访问和更新数据库页的缓冲区管理器，以及用于减少数据库文件 I/O 的缓冲区缓存（也称为缓冲池）。

缓冲区是内存中的 8 KB 页，大小与数据页或索引页相同。因此，缓冲区缓存分为 8 KB 页。缓冲区管理器管理将数据或索引页从数据库磁盘文件读取到缓冲区缓存以及将修改的页写回磁盘的功能。页将保留在缓冲区缓存中，直到缓冲区管理器需要缓冲区来读取更多数据。仅当数据被修改时，数据才会写回磁盘。缓冲区缓存中的数据在写回磁盘之前可以多次修改。

当 SQL Server 启动时，它会根据多个参数（如系统上的物理内存量、配置的最大服务器线程数和各种启动参数）计算缓冲区缓存的虚拟地址空间的大小。SQL Server 为缓冲区缓存保留此计算量的进程虚拟地址空间（称为内存目标），但它仅获取（提交）当前加载所需的物理内存量。您可以查询sys.dm_os_sys_info目录视图中的committed_target_kb列和committed_kb列，以分别返回保留为内存目标的页数和缓冲区缓存中当前提交的页数。

SQL Server 启动和缓冲区缓存获取其内存目标之间的时间间隔称为上升。在此期间，读取请求会根据需要填充缓冲区。例如，单个 8 KB 页读取请求填充单个缓冲区页。这意味着上升取决于客户端请求的数量和类型。通过将单页读取请求转换为对齐的八页请求（构成一个范围）来加快提升速度。这使得爬坡可以更快地完成，尤其是在具有大量内存的机器上。

由于缓冲区管理器使用 SQL Server 进程中的大部分内存，因此它与内存管理器配合使用，以允许其他组件使用其缓冲区。缓冲区管理器主要与以下组件交互：

资源管理器，用于控制总体内存使用情况，在 32 位平台中，用于控制地址空间使用情况。用于低级别文件 I/O 操作的数据库管理器和 SQL Server 操作系统 （SQLOS）。用于预写日志记录的日志管理器。

缓冲区管理器可识别非一致性内存访问 （NUMA）。缓冲区缓存页分布在硬件 NUMA 节点上，这允许线程访问在本地 NUMA 节点上分配的缓冲区页，而不是从外部内存访问。缓冲区管理器支持热添加内存，允许用户在不重新启动服务器的情况下添加物理内存。缓冲区管理器支持 64 位平台上的大页面。页面大小特定于 Windows 版本。缓冲区管理器提供通过动态管理视图公开的额外诊断。可以使用这些视图监视特定于 SQL Server 的各种操作系统资源。

缓冲区管理器仅执行对数据库的读取和写入。其他文件和数据库操作（如打开、关闭、扩展和收缩）由数据库管理器和文件管理器组件执行。

缓冲区管理器的磁盘 I/O 操作具有以下特征：

所有 I/O 都是异步执行的，这允许调用线程继续处理，而 I/O 操作在后台进行。所有 I/O 都在调用线程中发出，除非正在使用关联 I/O 选项。关联性 I/O 掩码选项将 SQL Server 磁盘 I/O 绑定到指定的 CPU 子集。在高端 SQL Server 联机事务处理 （OLTP） 环境中，此扩展可以增强发出 I/O 的 SQL Server 线程的性能。多页 I/O 通过分散-收集 I/O 完成，这允许将数据传入或传出不连续的内存区域。这意味着 SQL Server 可以快速填充或刷新缓冲区缓存，同时避免多个物理 I/O 请求。

缓冲区管理器报告任何未完成至少 15 秒的 I/O 请求。这有助于系统管理员区分 SQL Server 问题和 I/O 子系统问题。报告错误消息 833，并显示在 SQL Server 错误日志中。长 I/O 可以是读取或写入;消息中当前未指示。长 I/O 消息是警告，而不是错误。它们并不表示 SQL Server 的问题，而是表示底层 I/O 系统的问题。报告这些消息是为了帮助系统管理员更快地找到 SQL Server 响应时间不佳的原因，并区分 SQL Server 无法控制的问题。因此，它们不需要任何操作，但系统管理员应调查 I/O 请求花费这么长时间的原因，以及时间是否合理。

较长的 I/O 消息可能表示 I/O 被永久阻止且永远不会完成（称为丢失 I/O），或者只是表示它尚未完成。无法从消息中判断出哪种情况，尽管丢失的 I/O 通常会导致闩锁超时。

长 I/O 通常表示 SQL Server 工作负荷对于磁盘子系统来说过于密集。在以下情况下，可能指示磁盘子系统不足：
在繁重的 SQL Server 工作负荷期间，错误日志中会出现多个长 I/O 消息。性能监视器计数器显示较长的磁盘延迟、较长的磁盘队列或无磁盘空闲时间。

长 I/O 也可能是由 I/O 路径中的组件（例如，驱动程序、控制器或固件）不断推迟处理旧的 I/O 请求，转而处理更接近磁盘磁头当前位置的新请求引起的。根据哪些请求最接近读/写头的当前位置来优先处理请求的常用技术称为“电梯搜索”。这可能很难用性能监视器工具证实，因为大多数 I/O 都会及时得到维护。执行大量顺序 I/O 的工作负载（如备份和还原、表扫描、排序、创建索引、批量加载和清零文件）可能会加剧长 I/O 请求。

SQL Server 可识别非一致性内存访问 （NUMA），并且在没有特殊配置的 NUMA 硬件上表现良好。随着时钟速度和处理器数量的增加，减少使用这种额外处理能力所需的内存延迟变得越来越困难。为了规避这种情况，硬件供应商提供了大型 L3 缓存，但这只是一个有限的解决方案。NUMA 体系结构为此问题提供了可扩展的解决方案。

SQL Server 旨在利用基于 NUMA 的计算机，而无需进行任何应用程序更改。

到此这篇关于SQLServer的内存管理架构详解的文章就介绍到这了,更多相关SQLServer内存管理架构内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！