SQL Server数据库实例中的最小和最大内存配置

迪鲁宾
1,872 阅读7分钟

本文深入介绍了SQL Server数据库实例的最小和最大内存的配置。

简介

服务器的性能取决于各种因素，如CPU、内存和存储配置。SQL Server工作在操作系统之上，与这些资源密切相关。通常情况下，如果你RDP到SQL Server，你可能会注意到SQL Server是一个顶级的内存消耗者，即使在空闲的实例上。对于那些不熟悉SQL Server内存管理工作方式的人来说，这是一个相当常见的问题。

SQL Server的内存管理是动态的，DBA并不要求每个内存组件都有特定的内存配置。SQL Server根据工作负载的要求，使用缓冲区缓存从磁盘加载页面。有必要尽量减少磁盘I/O的要求。按照微软的文件，SQL Server的工作是平衡以下目标。

  • 保持缓冲池不至于大到整个系统的内存不足
  • 通过最大限度地增加缓冲池的大小，最大限度地减少对数据库文件的物理I/O

默认情况下，SQL Server会根据服务器的RAM动态地获取内存；但是，它确保不会给其余的进程造成内存短缺。

SQL Server数据库引擎有一个叫做资源监控器的后台进程。它的任务是监控内部和外部的内存指标。

这些通知被存储在环形缓冲区中。资源监控器的资源组使用信号RESOURCE_MEMPHYSICAL_HIGH*，* RESOURCE_MEMPHYSICAL_LOW*，* RESOURCE_MEMPHYSICAL_STEADY*，或者* RESOURCE_MEMVIRTUAL_LOW。

  • RESOURCE_MEM_STEADY：没有观察到内存问题。
  • RESOURCE_MEMPHYSICAL_HIGH: SQL Server可以获得内存，因为操作系统有足够的可用内存。
  • RESOURCE_MEMPHYSICAL_LOW: SQL Server必须归还内存，因为操作系统有内存不足的问题。
  • RESOURCE_MEMVIRTUAL_LOW：SQL Server进程的虚拟地址范围已经用尽。

一旦我们启动SQL Server，它就会计算出缓冲池的虚拟地址空间大小。它考虑到了物理内存、服务器线程、启动参数。SQL Server为当前的工作负载获取所需的内存。一旦用户开始执行他们的事务，它就会获得额外的物理内存，并持续到操作系统出现内存短缺。如果一个应用程序停止，并且有额外的内存可用，SQL Server实例就会调整其内存分配。

如果你打开SQL Server数据库实例属性，进入内存部分，你会发现以下配置。

  • 最小内存。默认值0 MB
  • 最大内存。默认值2147483647 MB

SQL Server Database instance

SQL Server数据库实例的最小服务器内存

它保证了特定实例的SQL Server的最小内存量。在SQL Service启动期间，它不会分配在最小内存配置中指定的任何内存。然而，一旦SQL Server由于客户端的工作负荷达到这个水平，它就不能释放内存给操作系统。

  • 注意。 ** 这是一个配置和保证内存的参数，特别是当你在同一台服务器上托管多个实例的时候
    • 看到SQL Server的内存使用率低于配置的最小服务器内存是正常的。*

它为缓冲池允许使用的内存设定了上界。这有助于为SQL Server进程、查询保留足够的内存。默认值允许SQL Server使用尽可能多的所需内存。它可能会消耗几乎所有的操作系统内存，这可能会引起服务器性能问题。

缓冲区缓存包括以下部分。

  • 数据库页面缓存
  • 锁管理器内存
  • 日志缓存
  • 查询工作负载空间
  • 优化查询
  • 系统数据结构

你可以查询DMVsys.dm_os_process_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;

Virtual Memory for SQL Server Database

假设你为SQL Server设置了最大内存，当你查看任务管理器时，你发现SQL进程占用的内存超过了配置的最大内存。一些组件，如线程堆栈、CLR、扩展过程、分布式查询、自动化对象和非SQL Server DLL，除了配置的最大服务器内存外，还占用了内存。

  • 注意：我们不应该在最小和最大服务器内存中指定相同的内存。如果你这样做，一旦内存达到配置的值，SQL Server就会停止动态管理内存。缓冲池使用整个内存

从SQL Server 2012开始，微软修改了内存分配，使内存使用更加公平和稳定。下表（参考MS docs）给出了SQL 2012版本前后的比较。

Type of memory allocations

SQL Server提供了多个参数，如页面寿命预期、缓冲区缓存命中率、服务器总内存、目标服务器内存。你可以使用DMV sys.dm_os_performance_counters或者PowerShell脚本来获取需要的计数器。

$counters = @("\Memory\Available MBytes",
"\Memory\Pages/sec",
"\SQLServer:Buffer Manager\Buffer cache hit ratio",
"\SQLServer:Buffer Manager\Lazy writes/sec",
"\SQLServer:Buffer Manager\Page life expectancy" 
'\SQLServer:Memory Manager\Total Server Memory (KB)'
'\SQLServer:Memory Manager\Target Server Memory (KB)'
) 
export_counters "$counters 
$collections = Get-Counter   -Counter $counters -SampleInterval 10 -MaxSamples 1
Write-Output $collections 
foreach ($collection in $collections) 
{$sampling = $collection.CounterSamples | Select-Object -Property TimeStamp, Path, Cookedvalue 
    $sampling | Format-Table -AutoSize
    }

PowerShell script output for SQL Server database

假设服务器总内存（KB）已经达到了目标服务器内存（KB）。在这种情况下，SQL Server可能会分配比最大配置值更多的内存，以满足多页内存请求（超过8KB）的要求。一旦分配完毕，工作完成，资源监控器就会向内存消耗者发出信号，释放额外分配的内存。它试图使服务器总内存低于目标服务器内存配置。你也可以监控性能计数器Total Server Memory(KB)和Target Server Memory(KB)进行分析。

最大服务器内存的一般准则

你不应该把所有的服务器内存分配给最大SQL内存配置。该配置应该为操作系统进程和其他在服务器上运行的应用程序（如果有的话）留下足够的自由空间。作为一般准则，对于单个实例来说，内存配置应该是如下规定。

  • 为操作系统留出至少1-4GB的空间
  • 最大服务器内存=（服务器总内存-操作系统内存）-（堆栈大小*最大工作线程）。
    • 64位x64位架构的堆栈大小为2048KB，IA64为4096KB
    • 最大的工作线程取决于CPU、计算机架构和SQL Server的版本。下表（参考微软文档）显示了工作线程数据

Max Server Memory general guideline

多个SQL Server实例的内存配置

有时我们在一台服务器上安装多个SQL Server实例。正如我们前面所指定的，在默认情况下，SQL Server不控制最大内存配置。因此，如果你不配置SQL Server的内存配置，一个实例可能会分配所有的内存，而另一个实例可能面临内存不足的问题。SQL Server不会在多个实例之间自动平衡内存。两个实例都会与Windows内存通知信号协调，以调整它们的内存。因此，你不应该为服务器上的各个实例也留下默认配置。

  • 配置最大服务器内存。你可以为每个SQL实例配置最大内存。但是，两个实例的总内存分配不应超过总的物理内存

    • 在这种方法中，活动实例可能会立即消耗服务器的最大内存。然而，如果一个实例没有运行多个实例，其他实例可能不会利用剩余的空闲内存

  • 配置最小服务器内存。建议为多个实例配置最小的服务器内存。多个实例的最小内存之和应该比总的物理内存少1-2GB（对于操作系统）。

    • 在这种方法中，如果另一个实例闲置或停机，SQL Server可以为一个实例扩展内存；另一个实例可以从空闲的内存中获益。然而，如果一个实例已经占用了内存，那么该实例可能需要一些时间来释放内存

配置SQL Server数据库实例的最小和最大服务器内存

你可以利用SSMS GUI或T-SQL来配置最小和最大的服务器内存。下面的SQL脚本为一个SQL Server数据库实例配置最小2GB和最大4GB的内存。

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

总结

在这篇文章中，我们探讨了SQL Server数据库实例的最小和最大内存配置。这是一个至关重要的配置，现在SQL Server 2019在安装SQL Server时包含了该建议和配置。你应该评估你的SQL Server、操作系统和其他进程的内存需求并配置这些值。

avatar
文章
阅读
粉丝