自调整的数据库引擎(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:
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)”允许虚拟机的内存总量超过宿主服务器实际RAM总量。
内存过载时,虚拟化管理器是通过内存释放(Memory Ballooning)和内存分页(Memory Paging)两种方法来处理内存压力的。
内存释放是一个运行在Guest VM中的进程。它是VM工具之一,通过释放驱动(Ballooning Driver)安装到每个Guest VM中。它可以从Guest获取内存返还给宿主。
当宿主有内存压力时(内存过载时),会通知内存释放返还内存,如果返还的内存量或者返还的速度不足以让宿主机解决内存压力,则宿主会直接把Guest VM的内存分页到磁盘。
被分页的Guest性能会下降,同样其上运行的SQL Server也受影响,甚至缓冲池内存会降到0.
现在被微软支持的虚拟化管理器都提供了方法设定Guest VM的最小保留内存用于保证上面的SQL Server正常运行,所以在虚拟化时要合理设定。
同时为每个SQL Server设定最小服务器内存,也可以一定程序避免此问题。
7. 多实例的内存设置
一台机器上多个实例,每一个都必需设定合适的最小和最大服务器内存,并且最大服务器内存之和要小于物理RAM以保留足够内存给OS使用。
如果不设定最小服务器内存,当OS或者其它实例有内存压力时,此实例会主动收缩内存而导致性能问题。
总结
最重要的是:今时今日你还有什么理由不用64位的系统!?