《Troubleshooting SQL Server》读书笔记-内存管理

9/1/2015来源:SQL技巧人气:854

《Troubleshooting SQL Server》读书笔记-内存管理

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

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

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

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

SQL Server是怎么分配内存的

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

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

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

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

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

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

内存压力分类,根据Memory PRessure - Classified

image

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

  • 服务器上安装的内存量
  • 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。然后确保性能计数器Memory\Available 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 Manager\Total Server Memory查看总的内存用量。

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

3. 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的启动时间显著增长。

要严格测试性能受益情况,并尽量在SQL Server专用服务器上启用。

诊断内存压力

通过性能计数器和DMV来判断系统是否有在在内存压力。需要谨记的一条:通过一两个性能计数器,是不能确定任何系统压力的,要全面的分析。

SQL Server:Buffer Manager下的性能计数器

1. Buffer Cache Hit Ratio

建议值是OLTP>=95%,OLAP>=90.这个计数器本身并不能说明SQL Server有内存压力,>=95%只是说明了SQL Server按设计的那样执行了数据页的预读预取。

2. Page Life Expectancy

以秒为单位,代表高速缓存的页过期并所占空间被重用的时间。

3. Free Pages

SQL Server缓冲池中的空闲页数量。当Page Life Expectancy计数器持续下降,Free Pages接近0,Free List Stalls持续大小0,则是表示明显的内存压力。

4. Free List Stalls/Sec

每秒请求等待缓冲池中空闲页的次数。

5. Lazy Writes/sec

每秒被LazyWriter进程刷新的缓冲池数据页数量。发生Lazy Writes/sec的同时PLE和Free Pages较低,又发生Free List Stalls,则说明缺少RAM。

SQL Server:Memory Manager下的性能计数器

1. Total Server Memory (KB) 和Target Server Memory (KB)

前者表示SQL Server已经占用的内存量,后者表示SQL Server想要占用的内存量。后者大于前者时,证明SQL Server需要更多可用内存,也是内存压力标志之一。

2. Memory Grants Outstanding

成功获得workspace内存的进程总数。值太低表示有大量的用户活动或者负载过重,如果同时Memory Grants Pending值偏高,则也是内存压力的标志之一。

3. Memory Grants Pending

正在等待被授予workspace内存的进程总数。

内存相关的DMVs

sys.dm_exec_query_memory_grants,sys.dm_os_memory_cache_counters,sys.dm_os_sys_memory,sys.dm_os_memory_clerks

常见的内存相关的问题

1. SQL Server内存泄露的误区

SQL Server看起来部会吃掉服务器尽可能多的内存,这不是内存泄露。扩展存储过程或者链接服务器驱动的内存泄露,可能会导致SQL Server无限制地去获取内存。

2. 分页问题

从SQL Server 2005 SP2后,当SQL Server进程的WirkingSet被收缩并分页到磁盘上时,错误日志中会写入"a significant part of SQL Server process memory has been paged out."

可能的原因有:

1.设定了不正确的最大服务器内存,并且未启用锁定内存页

2.Windows执行非缓冲的IO操作占用了大量的系统调整缓存,如拷贝文件

3.硬件驱动问题导致的内存过量使用或内存泄露

参考KB918483并找到进程工作集被收缩的根本原因并解决之。对于SQL Server而言启用锁定内存页,是取后也是解决此问题的唯一手段。

3. 启用了锁定内存页但未限制最大服务器内存导致OS不稳定

启用了锁定内存页但未限制最大服务器内存或者设定了过高的值,SQL Server会占尽可用内存,从而导致OS缺少内存而不稳定甚至崩溃。

启用了锁定内存页,一定要限制最大服务器内存,留下合适的内存量给OS使用。

4. 应用程序域标记为卸载导致内存压力(App Domain is marked for unload due to memory pressure)

这是SQLCLR相关的错误,通常在32位SQL Server上发生,也可能在设定了最大服务器内存,限制了SQLCLR的可用VAS的64位SQL Server上发生。

一般是由于SQLCLR程序集低效的内存使用方式和SQLCLR可用的VAS受限引起。

32位上发生此错误,建议升级到64位,以使用更多的用户模式VAS。但是如果因为SQLCLR程序集使用大内存对象(如DataSet)导致,升级可能也解决不了问题,

把SQLCLR代码做成独立的控制台或者WinForm程序并正确配置其内存使用。

如果SQLCLR存储整个执行的状态,但是代码访问安全性(Code access Security)定义为UNSAFE,则卸载会丢失状态信息导致更严重的问题。

解决此问题最好是升级到64位,临时的解决方案是使用-g启动参数增加MemToLeave内存给SQLCLR使用。

5. 701错误和FAILED_VIRTUAL_RESERVE

当SQL Server分配一段连续的VAS区域失败时,就会报此错误并输出请求分配的大小。

通常这个错误只出现在MemToLeave受限的32位系统上,因为各种需要分配大于8KB的操作类型,如设定了过大备份缓存,xml,SQLCLR,空间数据类型和链接服务器等。

解决此问题最好是升级到64位,临时的解决方案是使用-g启动参数增加MemToLeave内存。

6. 过渡分配的虚拟机

今时今日,SQL Server虚拟化已经很常见了。虚拟化的管理器提供的高级功能“内存过载(Memory Overcommit)”允许虚拟机的内存总量超过宿主服务器实际RA