SQL Server内存使用的简单介绍

栏目:云星空知识作者:金蝶来源:金蝶云社区发布:2024-09-16浏览:1

SQL Server内存使用的简单介绍

SQL Server内存使用的简单介绍


场景:

  1. SQL Server为什么内存占用越来越多?

  2. 怎么设置SQL Server的最大内存?怎么限制数据库的内存占用?

  3. SQL Server配置了最大内存占用,为什么实际占用超过了配置的值?

  4. SQL Server内存占用应该设置多大才合理?

  5. 等等其他能从理论中发现的隐藏flag... 


1 SQL Server 内存体系结构

SQL Server 将根据需要动态获取并释放内存。 虽然该选项仍然存在且在有些环境下需要用到,但通常情况下管理员不必指定应为 SQL Server 分配多少内存。
所有数据库软件的主要设计目标之一是尽量减少磁盘 I/O,因为磁盘的读取和写入操作占用大量资源。 SQL Server 在内存中生成缓冲池,用于保存从数据库读取的页。 SQL Server 中的大量代码专门用于尽量减少磁盘与缓冲池之间的物理读写次数。 SQL Server 设法在以下两个目标之间达到平衡:
  • 防止缓冲池变得过大,从而导致整个系统内存不足。

  • 尽量增加缓冲池达的大小,以便尽量减少数据库文件的物理 I/O。

在负载过重而内存不足的系统中,对于查询计划中带有合并联接、排序和位图的查询,如果无法获得位图所需的最小内存量,可以删除位图。 这会影响查询性能,并且如果排序过程无法容纳在内存中,就会增加 
tempdb 数据库中工作表的使用量,从而导致tempdb 增大。 要解决此问题,可添加物理内存或优化查询以使用其他更快速的查询计划。

2 动态内存管理

        SQL Server 数据库引擎的默认内存管理行为是在不造成系统内存不足的情况下获取尽可能多的内存。 SQL Server 数据库引擎通过使用 Microsoft Windows 中的内存通知 API 来实现这一点。
        SQL Server 动态使用内存时,将会定期查询系统以确定可用内存大小。 保持此可用内存可避免操作系统 (OS) 进行分页。 如果可用内存较少,SQL Server 将会释放内存以供操作系统使用。 如果有更多的内存可用,SQL Server 可能会分配更多的内存。 SQL Server 仅在其工作负荷需要更多内存时才增加内存;服务器空闲时不会增加其虚拟地址空间的大小。 如果发现当 SQL Server 使用动态内存管理时,任务管理器和性能监视器显示可用内存在稳步减少,这属于默认行为,而不应被视为内存泄漏。
        “最大服务器内存控制 SQL Server 内存分配、编译内存、所有缓存(包括缓冲池)、查询执行内存授予锁管理器内存和 CLR1 内存(实质上是 sys.dm_os_memory_clerks 中存在的任何内存分配器)。
        从 SQL Server 2012 (11.x) 开始,CLR 内存在 max_server_memory 分配下管理。
        以下查询返回有关当前分配内存的信息:
-- sql 查询当前分配内存 --
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;
        SQL Server 使用内存通知 API QueryMemoryResourceNotification 来确定 SQL Server 内存管理器何时可以分配内存和释放内存。
        SQL Server 启动时,将根据多个参数(例如系统的物理内存大小、服务器线程数和各个启动参数)计算缓冲池的虚拟地址空间大小。 SQL Server 将为缓冲池保留计算得到的进程虚拟地址空间量,但它仅为当前负荷获取(提交)所需的物理内存量。
       然后实例将继续获取支持工作负荷所需的内存。 随着用户连接和运行查询的逐步增多,SQL Server 将按需获取更多的物理内存。 SQL Server 实例将继续获取物理内存,直到达到自己的“最大服务器内存 (MB)”分配目标或操作系统指示不再有可用剩余内存;如果该实例获取的内存超过“最小服务器内存”设置,并且操作系统指示可用内存短缺,将释放内存。

随着在运行 SQL Server 实例的计算机上启动其他应用程序,这些应用程序将会占用内存,从而使可用物理内存量降到 SQL Server 目标以下。 SQL Server 实例将调整其内存使用量。 如果另一个应用程序已停止,并且可用内存增多,SQL Server 的实例会增加其内存分配的大小。 SQL Server 每秒可以释放和获取几 MB 的内存,从而根据内存分配变化快速做出调整。


min server memory 和 max server memory 的影响
        “最小服务器内存”和“最大服务器内存”配置选项确定了缓冲池和数据库引擎的其他缓存所用内存大小的上限和下限。 缓冲池不会立即获取“最小服务器内存”中指定的内存量。 缓冲池启动时只使用初始化所需的内存。 随着 SQL Server 数据库引擎工作负荷的增加,将会继续获取支持工作负荷所需的内存。 在达到“最小服务器内存”指定的内存大小之前,缓冲池不会释放其获取的任何内存。 达到最小服务器内存后,缓冲池将使用标准算法,根据需要来获取和释放内存。 唯一的区别是缓冲池从不将内存分配降到“最小服务器内存”设置水平下,也从不获取超过“最大服务器内存 (MB)”设置水平的内存。
        SQL Server 数据库引擎获取的内存大小完全取决于放置在实例上的工作负荷。 不处理很多请求的 SQL Server 实例可能永远不会达到“最小服务器内存”。
        如果为“最小服务器内存”和“最大服务器内存 (MB)”指定的值相同,则一旦分配给 SQL Server 数据库引擎的内存达到该值,SQL Server 数据库引擎将停止为缓冲池动态释放和获取内存。
        如果在运行 SQL Server 实例的计算机上频繁启动或停止其他应用程序,启动这些应用程序所需的时间可能会因 SQL Server 实例分配和释放内存而延长。 另外,如果 SQL Server 是几个在一台计算机上运行的服务器应用程序中的一个,系统管理员可能需要控制分配给 SQL Server 的内存量。 在这些情况下,可以使用“最小服务器内存”和“最大服务器内存 (MB)”选项控制 SQL Server 可以使用的内存大小。 “min server memory”和“max server memory”选项均以 MB 为单位指定。

3 服务器内存配置选项

        SQL Server 数据库引擎的内存利用率由一对配置设置进行限制:“最小服务器内存(MB)”和“最大服务器内存(MB)”。 随着时间的推移,在正常情况下,SQL Server 将尝试申请内存,使其达到“最大服务器内存(MB)”设置的限制。
        在这些边界内,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 的所有版本中,内存最大可配置为进程虚拟地址空间限制。
3.1 最小服务器内存
        使用“最小服务器内存(MB)”可保证可供 SQL Server 内存管理器使用的最小内存量。
    • SQL Server 不会在启动时立即分配在“最小服务器内存(MB)”中指定的内存量。 不过,除非调低“最小服务器内存(MB)”的值,否则当内存使用量由于客户端负载而达到该值后,SQL Server 不能释放内存。 例如,在同一台服务器上同时安装多个 SQL Server 实例时,请考虑设置“最小服务器内存(MB)”参数,使其为实例预留内存。

    • 为了确保来自基础主机的内存压力不会尝试从来宾虚拟机 (VM) 上的缓冲池释放超过可接受性能所需的内存,在虚拟环境中设置“最小服务器内存(MB)”值非常有必要。 理想情况下,虚拟机中的 SQL Server 实例不必与虚拟主机主动内存解除分配进程竞争。

    • SQL Server 并不一定分配“最小服务器内存(MB)”中指定的内存量。 如果服务器上的负载从不需要分配“最小服务器内存(MB)”中指定的内存量,则 SQL Server 将使用更少的内存。

3.2 最大服务器内存

        使用“最大服务器内存(MB)”保证 OS 和其他应用程序不会遇到来自 SQL Server 的不利内存压力。
    • 在设置“最大服务器内存(MB)”配置之前,在正常操作期间监视托管 SQL Server 实例的服务器的总体内存消耗,以确定内存可用性和要求。 对于初始配置,或者当没有机会收集一段时间内的 SQL Server 进程内存使用情况时,请使用以下通用最佳做法方法,为单个实例配置最大服务器内存 (MB):

    • 从总 OS 内存中减去“最大服务器内存(MB)”控制之外的潜在 SQL Server 线程内存分配量的同等值,这个量是堆栈大小1乘以计算出的最大工作线程数2。

    • 然后减去在“最大服务器内存 (MB)”控制范围外的其他内存分配的 25%,例如备份缓冲区、扩展存储过程 DLL、使用自动化过程(sp_OA 调用)创建的对象以及来自链接服务器提供程序的分配。 这是一个一般近似值,实际情况可能会有所不同。

    • 对于单个实例设置,剩下的应该就是“最大服务器内存(MB)”设置。

3.3 手动设置选项

        可将“最小服务器内存(MB)”和“最大服务器内存(MB)”设置为固定内存值。
        在需要兼顾同一台主机上运行的其他应用程序或其他 SQL Server 实例的内存要求时,此方法对于配置 SQL Server 实例的系统或数据库管理员来说非常有用。
方法1:使用 Transact-SQL
--使用 Transact-SQL,设置最小和最大服务器内存-- 
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 12288;
GO
RECONFIGURE;
GO

--查询返回当前配置的值和当前使用的值的相关信
SELECT [name], [value], [value_in_use]
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' OR [name] = 'min server memory (MB)';

方法2:使用 SQL Server Management Studio
  1. 在对象资源管理器中,右键单击服务器并选择 “属性” 。

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

  3. 在“服务器内存”选项中,输入最小服务器内存和最大服务器内存所需的数字。

image.webp

3.4 “锁定内存页”

        基于 Windows 的应用程序可使用 Windows 地址窗口扩展 (AWE) API 来分配物理内存并将其映射到进程地址空间。 LPIM Windows 策略将确定哪些帐户可以访问 API 以将数据保留在物理内存中,从而阻止系统将数据分页到磁盘的虚拟内存中。 使用 AWE 分配的内存被锁定,直到应用程序显式释放该内存或退出。 
注意:
       使用 LPIM 可实现根据其他内存分配器的请求扩大或缩小内存,不影响 SQL Server 动态内存管理。 使用“锁定内存页”用户权限时,强烈建议为最大服务器内存 (MB) 设置上限。 

        将 LPIM 与未考虑系统中其他内存消耗者的错误配置的最大服务器内存 (MB) 设置一起使用可能会导致不稳定,具体取决于其他进程所需的内存量,或超出最大服务器内存 (MB) 范围的 SQL Server 内存要求。

3.4.1 启用“锁定内存页”选项
此 Windows 策略将确定哪些帐户可以使用进程将数据保留在物理内存中,从而阻止系统将数据分页到磁盘的虚拟内存中。
  1. 在“开始”菜单上,选择“运行”。 在“打开” 框中,键入 gpedit.msc 将打开 “组策略” 对话框。

  2. 在“本地组策略”控制台中,展开“计算机配置”。

  3. 展开 Windows 设置。

  4. 展开“安全设置”。

  5. 展开“本地策略”。

  6. 选择 “用户权利指派” 文件夹。 细节窗格中随即显示出策略。

  7. 在窗格中,滚动到“锁定内存页”策略并双击它。

  8. 在“本地安全策略设置”对话框中,选择“添加用户或组...”。 添加 SQL Server 服务帐户。 若要确定 SQL Server 实例的服务帐户,请参阅 SQL Server 配置管理器或在 sys.dm_server_services 中查询 service_account

  9. 重启实例,使此设置生效。

3.4.2 查看“锁定内存页”状态
-- 查看“锁定内存页”状态
SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;
--sql_memory_model_desc 的以下值指示 LPIM 的状态:
------CONVENTIONAL。 未授予“锁定内存页”权限。
------LOCK_PAGES。 已授予“锁定内存页”权限。
------LARGE_PAGES。 已在启用了跟踪标志 834 的企业模式中授予“锁定内存页”权限。 这是一种高级配置,不建议对大多数环境使用。

--使用以下方法确定 SQL Server 实例是否正在使用锁定页
----检查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';

4 常见疑问

问题1:设置了最大内存占用,但是为什么实际内存占用略大于设置值。

① 从 SQL Server 2012 (11.x) 开始,SQL Server 可能会分配比“最大服务器内存 (MB)”设置中指定的值更多的内存。 当“总服务器内存 (KB)”值已达到“目标服务器内存 (KB)”设置(由“最大服务器内存 (MB)”指定)时,则可能会出现这种情况。 如果因内存碎片造成连续空闲内存不足,无法满足多页内存请求的需求(超过 8KB),SQL Server 可以执行超额承诺使用量,而不是拒绝内存请求。
执行此分配后,资源监视器后台任务会立即开始向所有内存消耗者发送信号,指示其释放已分配的内存,并尝试使“总服务器内存 (KB)”值低于“目标服务器内存 (KB)”设置。 因此,SQL Server 内存使用情况可短暂超过“最大服务器内存 (MB)”设置。 在这种情况下,“总服务器内存 (KB)”性能计数器读数将超过“最大服务器内存 (MB)”和“目标服务器内存 (KB)”设置。
在以下操作中通常会观察到此行为:
  • 大型列存储索引查询

  • 大型行存储上的批处理模式

  • 会使用大量内存来执行哈希和排序操作的列存储索引(重新)生成

  • 需要较大内存缓冲区的备份操作

  • 需要存储较大输入参数的跟踪操作

② SQL Server 进程将获取超过“最大服务器内存 (MB)”选项指定值的内存。 内部和外部组件都可以分配缓冲池以外的内存,这将占用额外的内存,但是分配给缓冲池的内存通常仍在 SQL Server 占用的内存中占最大份额。        

SQL Server内存使用的简单介绍

SQL Server内存使用的简单介绍场景:SQL Server为什么内存占用越来越多?怎么设置SQL Server的最大内存?怎么限制数据库的内存占用?SQL...
点击下载文档
确认删除?
回到顶部
客服QQ
  • 客服QQ点击这里给我发消息