SQL Server 性能调优（内存）

 
存储引擎自调整
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中内存的文章介绍了不同类型的内存压力，大致如下：
 

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 动态性能视图可以查看可用的虚拟地址空间。
保留空间的碎片问题是最难处理的，如果你打电话到微软技术支持，他们给的建议就是升级到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，
因为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 的异常状况。
 

作者 Amaranthus