SQL Server 性能调优(内存)

  1. 云栖社区>
  2. 博客>
  3. 正文

SQL Server 性能调优(内存)

技术小胖子 2017-11-15 22:12:00 浏览612
展开阅读全文

存储引擎自调整

sql server 是如何分配内存的

32bit地址空间的限制

用户模式vas分配和virtualalloc

boffer pool 分配内存(保留内存)

VAS调整

AWE

启动参数-g

诊断内存压力

内存相关计数器

SQL Server :Buffer Manager

buffer cache hit ratio

page life expectancy

Free Pages

Free list stalls/sec

lazy write/sec

SQL Server:memory Manager

total server memory  target server memory

memory grants outstanding

memory grants pending

内存相关的DMV

内存相关问题

分页问题

因为lock pages 没有设置服务器最大内存导致系统不稳定

701错误和 FAILED_VIRTUAL_RESERVE

多实例下的内存设置

总结

 

内存的分配和使用在网上一直是讨论的话题。对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 体系结构,32b64b

4.sql server 配置项

5.sql server 版本



32bit地址空间的限制

对于sql server 32位最大的影响就是32位的地址空间,也就是最大4g而且包含内核模式和用户模式。用户模式和内核模式各2g。主要我们讨论一下几点

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 在正常的备份下,需要16backup buffer,每一个buffer4m的内存。所以会吃掉64m的非buffer pool 内存。为了保证有住够非buffer pool32bsql server 在启动的时候会保留一部分内存。一旦有保留内存,那么buffer pool的内存空间会是安装的空间减去保留的空间。对于20052008保留空间为 maxworkerthreads*0.5mb+256mb(默认保留空间大小)maxworkerthreads = processprcount-4+256。在2000 maxworkerthreads = 256,按这个计算保留空间至少是384MB,但是通常少于432mb。保留空间的参数在sql server 启动参数内设置,标记为-g,可是适当在计算出来的结果上增加内存。sql server 一共2g的地址空间减去保留的,大概剩下1.6Gbuffer 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对于 worksettrim通常是下列的情况:

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错误和答应出需求大小的信息。这个错误只会发生在32bsql server32b 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 又很大的不同,如果负载较高那么就是用64b6号那天sql server 2012发布,还没去看联机文档,但是听朋友说201232b不在支持awe,也就是说如果大内存那么就用64b64b内存方面很有优势。这篇主要讲了内存的状况,总结到这里我已经明显的感觉到,常见问题远没有原理来的重要。troubleshooting只是原理的一种应用而已。关于资源的都讲完了,cpu,内存,io,下一篇会讲讲miss indexmiss index 也是会引起 cpu,内存,io 的异常状况。

 

 

 


    本文转自 Fanr_Zh 博客园博客,原文链接:http://www.cnblogs.com/Amaranthus/archive/2012/03/09/2387594.html,如需转载请自行联系原作者





网友评论

登录后评论
0/500
评论
技术小胖子
+ 关注