SQL Server 性能调优(内存)
total server memory 和 target server memory
因为lock pages 和没有设置服务器最大内存导致系统不稳定
内存的分配和使用在网上一直是讨论的话题。对sql server 来说使用了内存但是不释放是很正常的事情,和其他的应用程序是不一样的,导致一些用户认为sql server确实内存但事实并非如此。
存储引擎自调整
从sql server 2005开始,内存的管理就是动态的,与其他关系型数据库不同使用已经调整好的内存空间。如plan cache 是全部的并且自动的,引擎控制根据当前数据库的负载和其他活动信息来控制plan chache内存。sql server 虽然缺少内存的控制方法,但是还有有参数可以设置内存的,如操作系统的版本,内存的大小和处理器的体系结构。
sql server 是如何分配内存的
第一反应就是查看windows任务管理关于sql server 的内存使用情况,一看到sql server 占用了很大的内存就可能会认为sql server 缺少内存,但是缺少内存和占用很大内存其实是没什么关系的。sql server是被设计为大内存使用的,如buffer cache,存放了大量的数据页,为了减少io,提高性能。通常,不管你提供了多少内存sql server 都能使用光,除非你接到一个来自操作系统的memory low通知,sql server 就会自动调节减小内存,通知有2中:
memory high:通知sql server可以增加内存的使用量
memory low:通知sql server 释放内存
如果windows 不通知,那么sql server 就不会增加或减少内存,在windows 2003 和 sql server 2005 以前的版本是没有的。有一篇关于sqlos中内存的文章介绍了不同类型的内存压力,大致如下:
文章地址:http://blogs.msdn.com/b/slavao/archive/2005/02/01/364523.aspx
sql server 最大内存使用量由以下几点要素:
1.安装的物理内存数量
2.操作系统的最大内存限制
3.sql server 体系结构,32b,64b
4.sql server 配置项
5.sql server 版本
32bit地址空间的限制
对于sql server 32位最大的影响就是32位的地址空间,也就是最大4g而且包含内核模式和用户模式。用户模式和内核模式各2个g。主要我们讨论一下几点
1.sql server 用户模式如何分配内存
2.非buffer pool的保留空间
3.用户模式使用3g 内存
4.sql server 如何使用大于4g内存,data cache调用awe内存。
接下来主要讲的都是32bit 下内存使用的限制和64bit关系不大。
用户模式vas分配和virtualalloc
sql server 为用户默哀是保留了2g的内存地址,sql server 用户模式需要内存时,通过调用virtualalloc 分配内存并返回32位的指针,因为地址空间的限制,所以sql server 只能使用2g内存。
通过virtualalloc分配的内存并不一定是实际的物理内存,不管安装了多少物理内存,sqlserver都是2g的地址使用空间。windows也保证sql server 和其他应用程序使用的内存,不会超过实际物理内存和页面文件的总容量。如果总共安装的内存少于2g,那么sql server 就会又物理内存的限制,sql server buffer pool的内存也不会超过安装的内存数量。virtualalloc 分配的内存都是页模式的,也就是如果出现内存压力windows 就可以直接把内存写入到磁盘中。
非boffer pool 分配内存(保留内存)
如果sql server 请求大于8k的连续内存的时候,会使用多页分配器分配内存。backup buffers 是非buffer pool分配的最大的一个,需要的内存是 maxtransfersize * backupbuffercount 在正常的备份下,需要16个backup buffer,每一个buffer又4m的内存。所以会吃掉64m的非buffer pool 内存。为了保证有住够非buffer pool,32b的sql server 在启动的时候会保留一部分内存。一旦有保留内存,那么buffer pool的内存空间会是安装的空间减去保留的空间。对于2005和2008保留空间为 maxworkerthreads*0.5mb+256mb(默认保留空间大小),maxworkerthreads = (processprcount-4)+256。在2000 中maxworkerthreads = 256,按这个计算保留空间至少是384MB,但是通常少于432mb。保留空间的参数在sql server 启动参数内设置,标记为-g,可是适当在计算出来的结果上增加内存。sql server 一共2g的地址空间减去保留的,大概剩下1.6G供buffer pool 使用。对于超过4gb的内存可以使用awe来分配内存。
VAS调整
对于4g的内存,系统可以修改内核的地址空间使用率把1:1,变为1:3。这个叫做4g调整,这样就减少了内核模式下的地址空间,导致了PTE减小,pte是虚拟内存和物理内存的映射,一但减小,sql server 总共可使用的内存也就背减少了。所以在调整的时候要谨慎。在windows 2008下可以使用bcdedit /set 命令设置increaseuserva 可以设置从 2048 到 3072的值。在2000 到 2003 可以使用/3g的标签来开启。
AWE
如果安装了超过了4个的内存,那么就可以使用awe,当然windows 必须支持才能使用。开启pae,系统最多能够使用64G内存。在内存分配上因为使用virtualalloc分配内存会有限制,那么就改用allocateuserphysicalpage来分配内存,一旦被分配那么内存页被锁住,无法交换到交换文件。当启用awe后所有的datacache使用过awe分配内存还有就是 plancache也是。要启用awe那么pae就必须被设置,还有在sql server 的awe enabled 参数sql server 的启动用户必须有lock pages 权限。因为awe内存无法被交换出去,所以设置最大内存数量很偶必要,使得sql server 内存使用量得到限制,不会无限期的增长影响其他应用程序和系统内存的使用问题。在系统中如果你的内存少于16g,你可以使用前面提到的awe+4gt的方式,但是不推荐因为如果你一旦设置了4gt,windows 可管理内存从64g下降到了16g yinwei 4gt减少了pte的大小。
启动参数-g
在sql server 启动的时候会分配一部分保留内存,保留内存对buffer pool 来说是比较小的。很多的内存是从buffer pool上分配而不是从保留空间中分配,因此基本上不会有问题,但是随着日积月累应用程序变的越来越复杂,默认的保留空间已经无法满足需求那么就通过-g参数配置。因为不合适的空间大小和保留空间的碎片问题,导致无法请求到连续的内存空间。查看sys.dm_os_virual_address_dump 动态性能视图可以查看可用的虚拟地址空间。关于确定可用地址空间可以看相关文章:http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-servermemtoleave-vas-and-64-bit.aspx
保留空间的碎片问题是最难处理的,如果你打电话到微软技术支持,他们给的建议就是升级到64b,因为64b的虚拟地址空间是8t,不会不够如果你不想那么就加大你的保留空间地址。
诊断内存压力
当sql server 内存不足的时候,那么data cache 存储的数据就少,查询不能在内存中请求到数据,那么就请求io,放入内存,这些数据又很快的被清出内存,需要的时候有继续从io读进来,这个就是buffer pool 滚筒。buffer pool 滚筒会照成io过高,就会误认为是io的问题,其实是内存不足的问题。
内存相关计数器
有一些重要等待和内存使用率相关的性能计数器,但是要记清楚并没有一个计数器就能够表明内存压力的,一个简单的计数器快照并不能说明问题。内存压力的诊断需要一段时间的跟踪。
SQL Server :Buffer Manager
又很多有用的计数器都是这 buffer manager 对象下面,可以帮助发现buffer pool滚筒的问题。
buffer cache hit ratio
buffer cache hit ratio一般情况下在oltp中要高于95%,在olap中要高于90%。可惜的是没有关于这个性能指标相关的解释,和这个值是如何影响预读机制的。如果这个指标的值有巨大的下降那么就说明有问题。这个不能说明内存压力和sql server 健康指数。
page life expectancy
page life expectancy是页生命周期,也就是一个数据页在内存中的时间。在以前sql server 2000 4g的内存已经很大了,sql server buffer pool的大小是1.6g,如果sql server 从磁盘上读取1.6g的数据也只要5分钟,但是今天64g的内存是主流,如果从磁盘一下子读取50g的内存,会严重的冲击io。当存在大量的查询扫描表,读入新的数据页,导致生命周期值下降也不是不正常的。这个值必须长期的监视来分析问题。
Free Pages
free pages是内存中空页的数量,不要接近于0。这个值说明查询能否在其他查询不是放内存的情况下,快速的分配内存的主要依据。如果free pages 很少,页生命周期很短,并且伴随着空页争用(free list stalls/sec)的情况那么很有可能导致内存压力。
Free list stalls/sec
Free list stalls/sec每秒空页等待的数量,如果一段时间内都在0以上那么说明可能存在内存压力。
lazy write/sec
lazy write/sec 就是每秒写入磁盘的次数。如果发生量很大并且生命周期很短,free page 很少,但是 free list stall/sec 量很大,那么就是发生内存压力了。
SQL Server:memory Manager
SQL Server:memory Manager对象内对内存的消费和内存管理的问题提供了很重要参考
total server memory 和 target server memory
这2个计数器代表了当前sql server 使用的总共内存和sql server 想要用的内存。如果 target server memory超过了total server memory,也是内存压力的重要标志。sql server 会减少内存的需求来接近服务的可用内存,或者通过最大服务器内存配置,所以当内存出现压力问题的时候不应该第一时间去查看这2个计数器
memory grants outstanding
该值是现实多少进程已经成功的获取了内存的授权。在一段时间内,业务高峰期,如果该值过低,那么标志可能存在内存压力,特别是 memory grants pending 也比较高的情况下。
memory grants pending
该值是有过少进程正在等待内存的授权。如果为非0,那么说明需要调整或者优化负载或者增加内存。
内存相关的DMV
和内存相关的等待和非buffer pool 内存分配的信息,从dmv中获取。
sys.dm_exec_query_memory_grants 可以查看正在等待授权的查询,特别是大内存的授权
sys.dm_os_memory_cache_counter multi_pages_kb 显示了多页分配的内存分配
sys.dm_os_sys_memory 合计了系统当前内存,缓冲,cache,多页分配分配的内存。
sys.dm_os_memory_clerks 显示相关管理内存的书记进程,如 buffer pool 大内存的使用并且结合 MEMORYCLERK_SQLQERESERVATIONS 可以发现buffer pool 内存不住
内存相关问题
通常的一些问题可以被分为3种:错觉,错误配置,正在的问题。大量的疑似内存问题的最后其实只有一小部分才是真正的问题。
分页问题
当sql server 重要的组件被page out了,会在error log 中出现一个信息“a significant part of SQL Server process memory has been paged out”对于 workset的trim通常是下列的情况:
1.当没启用lock pages的时候,不正确的最大服务器内存的设置
2.windows 中系统缓冲,被用来处理非缓存的io操作,如复制文件。
3.硬件驱动问题导出使用过多的内存。
最有效的阻止方法是,开启lock pages,文章:http://support.microsoft.com/kb/918483讲述了64b sql server 发生workset trim的根本原因。
因为lock pages 和没有设置服务器最大内存导致系统不稳定
如果sql server开启了 lock pages 但是 最大服务服务内存又没设置,sql server 会吃光所有的服务器的可用内存。当windows 内存紧张会向通知sql server 内存压力,但是buffer pool 和 working set 都不会被交换页面文件。这样会导致windows crash。如果最大内存数设置的过大也会造成同样的情况。
701错误和 FAILED_VIRTUAL_RESERVE
当sql server 申请一个连续的vas失败,就会返回701错误和答应出需求大小的信息。这个错误只会发生在32b的sql server,32b sql server vas十分有限。这个错误和buffer pool 没有什么关系主要是大于8k内存分配的时候出现。解决办法就是使用-g启动参数,修改sql server保留空间。
多实例下的内存设置
sql server 如果多实例安装在单个机器上或者一个故障转移能减少license的购买。当一台服务器上有多个实例,那么设置min_server_memory 和max_server_memory 很重要,根据每个实例的负载,避免出现内存冲突的情况。根据先前提到过的性能指标和dmv 对内存使用情况监测,设置一个合理的最大内存和最小内存数。在多实例情况下,建议把最小内存数也设置上,因为如果有最小内存数,那么sql server 申请内存的时间会减少。如果最小内存数没有设置,sql server 可能会自愿减少内存的使用率而导致性能下降。
总结
在内存上面 32b 和64b 又很大的不同,如果负载较高那么就是用64b,6号那天sql server 2012发布,还没去看联机文档,但是听朋友说2012的32b不在支持awe,也就是说如果大内存那么就用64b。64b内存方面很有优势。这篇主要讲了内存的状况,总结到这里我已经明显的感觉到,常见问题远没有原理来的重要。troubleshooting只是原理的一种应用而已。关于资源的都讲完了,cpu,内存,io,下一篇会讲讲miss index,miss index 也是会引起 cpu,内存,io 的异常状况。
本文转自 Fanr_Zh 博客园博客,原文链接:http://www.cnblogs.com/Amaranthus/archive/2012/03/09/2387594.html,如需转载请自行联系原作者