优化 SQL Server 内存配置

澳门新葡亰手机版 1

1.最小和最大服务器内存

自调整的数据库引擎(Self-tuning Database Engine)

这两个配置用于控制 SQL Server 可用内存的大小。对于最小内存,在 SQL
Server
服务启动时,不会马上达到这个设置值,而是仅使用最小的需求内存,然后按需增长,一旦增长到最小内存设置值时,
SQL Server 将不会再释放内存。最大内存用于设置内存使用的上限,可以使用
SSMS 或者 sp_configure
来配置。需要提醒的是,这里的“最大内存”实际上指的是 Buffer Pool,在 64
位系统中,如果没有控制好内存而导致空闲的物理内存不足,会引起 Windows
削减 SQL Server 的工作集。

长期以来,微软都致力于自调整(Self-Tuning)的SQL
Server数据库引擎,用以降低产品的总拥有成本。从SQL Server 2005开始,SQL
Server就是动态管理内存使用,并且调整内存使用时,不需要重启数据库引擎。

如何计算合适的最大内存?可以参考以下信息。

所以它也不提供内存分配的微调项。各个组件的内存分配,完全由数据库引擎自动管理,不能手动分配。但是这货还是提供了一些配置项,能够影响数据库引擎如何使用内存。

监控 SQL Server 的最大内存使用情况

澳门新葡亰手机版,是否使用这些配置项来替代默认值,取决于操作系统版本,SQL
Server版本,可用物理内存和处理器架构等。

可以通过性能监视器的 MSSQL$instance:Memory Manager/Total Server Memory
(KB)计数器来监控 SQL Server 总的 Buffer Pool 使用情况。如果 SQL Server
所需的物理内存超过了现有的可用数量,这个值就会降低,而在释放内存后,这个值则会升高。可以在一开始把这个值设置得低一点,然后通过监控来适当地进行调整。

 

SQL Server 潜在的最大内存使用

SQL Server是怎么分配内存的

在考虑潜在使用时,很重要的一点是对连接服务器和外部存储过程的调用,在后期开发中,这部分内容可能会非计划地加入。一般来说,每个线程会使用
0.5MB或者2MB的内存,还要保证大概有 512MB 可以用于这部分的使用。

SQL
Server本身设计就会尽可能多的使用内存。正常情况下,它不会释放已经分配的内存,除非OS引发并设定Low
Memory资源通知标记(Resource Notification Flag)。

另外,一些大型企业可能会使用第三方备份软件、杀毒软件等,这些也会影响
SQLServer 的内存使用。最好预留 1 ~ 3GB 的内存给这些软件使用。

SQL Server
2005的SQLOS中添加了一个专用线程用于监控OS发出的内存通知(Memory
Notification)(这也是自调整功能之一)。

2. 检查最大内存配置是否合理

OS中有两种类型的内存通知:

在搭建新服务器时,该怎么去决定配置的最大服务器内存是多少呢?最直接的答案是:从低开始设置,然后进行周期性监控,并按需调整。或者使用性能计数器来监控,比如通过
MSSQL$instance:Buffer Manager/PageLife Expectancy (PLE) 和
Memory/Available Mbytes 来监控。

Memory High:SQL Server可以增加Working Set使用量并使用更多内存

PLE 计数器用于表示 SQL Server
的数据缓存在内存中的时间,在理想情况下该时间越长越好,这是内存压力指标之一。如果小于
300s,就要检查指标 Available Mbytes 了。

Memory Low:OS有内存压力,SQL Server释放一些内存给OS

Available Mbytes 表示 Windows
上当前有多少物理内存没有被使用。国外专家建议的标准是最少保留
100MB。当然不要把最低标准当作最低配置,应尽可能保证有 GB
级别的可用内存。

如果两种内存通知OS都没设定,则表明内存使用稳定,SQL
Server将继续在现有的进程空间内运行。但是这个功能在Windows 2003和SQL
Server 2005之前是没有的。

如果 PLE
很低,但是可用内存数很高,那么应该调高最大服务器内存,因为这样可以增加
PLE 的时间。相反,如果可用内存很低,但是 PLE
很高,那就需要降低最大内存配置来释放内存给 Windows。下面是部分配置示例。

内存压力分类,根据Memory Pressure –
Classified:

最大服务器内存 30GB,服务器有 32G RAM, PLE 平均值为
10000,可用内存为90MB,那么最少降低 500MB 最大服务器内存。

  
澳门新葡亰手机版 1

最 大 服 务 器 内 存 46GB, 服 务 器 有 50G RAM, PLE 平 均 值 为 10,
可 用 内 存 为1500MB,那么应提高最大服务器内存 500~ 1000MB。

SQL Server可以使用多少内存,取决于:

最大服务器内存 60GB,服务器有 60G RAM, PLE 平均值为 50,可用内存为
20M,那么应降低 100MB 最大内存,或者增加更多的 RAM。

  • 服务器上安装的内存量
  • Windows系统的内存限制
  • SQL Server的架构(32bit/64bit)
  • SQL Server控制内存使用的配置项
  • SQL Server的版本

 

32位VAS的限制 

Windows在VAS中运行每一个进程。32位的进程最多只可寻址到4GB内存,而这4GB内存又分为内核模式(Kernel
Mode)空间和用户模式(User Mode)空间。默认,windows会各分配2GB。

内核模式主要用于OS,用户模式用于当前执行的应用程序进程(例如SQL Server)。

  1. 用户模式VAS分配和VirtualAlloc

      SQL
Server保留的2GB用户模式VAS,当出现物理内存分配时才会提交。它是通过VirtualAlloc这个Windows
API。

      32位的SQL
Server或者Windows,调用VirtualAlloc返回一个32位的指针,这就是为什么SQL
Server只能使用到2GB用户模式VAS的原因。

     
通过VirtualAlloc分配的内存并不一定是实际物理内存,当分配的内存被提交时,才会是RAM的内存。提交内存时,windows要确认SQL
Server及其它应用程序进程提交的内存总量<=(RAM+分页文件)。

     
需要注意是VirtualAlloc分配的内存是可分页内存,意味着OS出现内存压力时,它们会被分页(page
out)到磁盘上。

  2. 非缓存池分配(MemToLeave)

     SQL
Server占用的大部分内存分配给了缓存池,用于缓存数据和查询计划。当需要大于8KB的连续页时,会通过多页分配器分配非缓存池,如LinkedServer,线程堆栈,CLR,备份缓存等。

     为了确保有足够的非缓存池内存,32位SQL
Server在启动时就会保留部分VAS。保留的部分也叫做MemToLeave,大小=MaxWorkerThread*0.5MB+256,其中MaxWorkerThread=(ProcessorCount-4)+256.

    
默认情况下MemToLeave=256*0.5+256=384MB,所以缓存区的大小约为(2GB-384MB)=1664MB。

  3. VAS调整(VAS Tuning)

   
在有4GB内存的服务器上,可以使用VAS调整使得用户模式VAS占到3GB,内核模式VAS减少为1GB.

    需要注意的是内核模式内存的减少,使得系统PTEs(Page Table
Entires)减少,造成系统不稳定,同时SQL Server可以寻址到的内存也变少了。

    Windows 2008上实现VAS Tuning,使用BCDEdit /set IncreaseUserVa
[value](value取2048到3072间的值)。

  4. AWE(Address windowing extension)

    在多于4GB RAM的服务器上,可以使用AWE让SQL
Server使用内存。使用AWE需先启用PAE,在windows 2008上使用BCDEdit /set PAE
ForceEnable启用。

    然后SQL Server开启”AWE
Enabled”,服务账号需要具有锁定内存页的权限(Lock Pages In
Memory,在组策略分配这个用户权限)。

   
AWE使内存管理的指针由32扩展到36位,所以最能寻址64GB内存。并且分配内存时,不使用VirtualAlloc而使用AllocateUserPhysicalPages函数。此API通过PTE直接分配物理内存。

    AWE的内存只能被缓冲池(Buffer
Pool)使用,并且是被锁定和不可分页的,所以最好使用设定“’max server
memory”来限制一下量。

  5. -g启动参数

    32位平台上可以使用SQL
Server的-g启动参数指定MemToLeave内存量,从而提高MemToLeave的内存分配量。但同时这也会减少缓存池的分配量。

 

使用64位的SQL Server

   
64位平台的VAS理论上限可达16EB=16,000,000TB,实际上X64限制在8TB,IA64为7TB。使用超过4GB
RAM时,SQL Server不用进行额外配置。

    SQL
Server使用的内存只能通过VAS提交,所以所有内存都是非锁定的和可分页的。这样当OS有内存压力时,这些内存可能会被分页到磁盘(hard
page out)。

   
VAS如此充足,MemToLeave的分配理论已经不再适用,同理-g启动参数也没有意义。

    过程缓存(Procedure
cache)也会存得更多,这可能会带来过程缓存过量的问题。

 

64位SQL Server的内存配置选项

1. 最小/大服务器内存

    SQL Server提供了两个实际级别的,限制缓存池大小的配置项:min server
memory/max server memory.需要注意的是从SQL Server 2000到2008
R2,这两个配置只对缓存池(Buffer pool)有效。

    在启用了“锁定内存页”时,两都的差值意味着:当有外部内存压力时,SQL
Server可以调整的范围。

   
设定最大值时,没有一个通用的值。初始化配置的基本原则:服务器内存<=16GB时,OS保留1GB,每4GB
RAM保留1GB;>=16GB时,OS保留1GB,每8GB RAM保留1GB.

   
例如:32GB的服务器,最大值=32-1-4=27GB。然后确保性能计数器MemoryAvailable
Mbytes介于150~300之间,逐渐调整max server memory。

2. 锁定内存页(lock pages in memory)

  
64位SQLOS默认使用VirtualAlloc分配所有的内存,此API分配的内存是非锁定和可分页的。当OS有内存压力时标记MemoryLow,
SQL Server会释放内存直到”最小服务器内存”。

  
如果它释放的速度不够快或者释放的量不满足于OS,则这些内存会被分页到分页文件。对于使用大内存的SQL
Server,WorkingSet分页对性能影响是非常严重的。

   启用锁定内存页,使得SQL Server分配缓存池内存时使用AWE API
AllocateUserPhysicalPages。此函数分配的内存是锁定的和不可分页的。

   而缓存池占用着SQL
Server大部分内存,所以启用锁定内存页会很大程度上避免WorkingSet分页。AWE
Enabled配置项在64位SQL Server是无效的空操作。

  
启用锁定内存页后,任务管理器的SQLServr.exe显示的是非缓存池内存用量。需要使用SQL
Server:Memory ManagerTotal Server Memory查看总的内存用量。

   启用锁定内存页是SQL Server 2005/2008/2008 R2企业版和2008
R2标准版的功能。在2008 SP1_CU2和2005
SP3_CU4更新后,也可以通过启用跟踪标记845来其它版本启用锁定内存页。

  1. LPA(Large Page Alloction)

  
在X64系统上,大页分配是指使用2MB的大小分配内存页,默认内存页是4KB。启用LPA需要满足条件:a).
SQL Server 企业版 b).服务器RAM>=8GB c).启用锁定内存页

   X64系统上,启用LPS(Large Page Support)和跟踪标记834,SQL
Server将使用大页分配缓存池内存,并且SQL Server的启动时间显著增长。