浅谈SQL Server对于内存的管理(1)
发布时间：2015-09-18 00:00 来源：未知
简介
理解SQL Server对于内存的管理是对于SQL Server问题处理和性能调优的基本，该文文章讲述SQL Server对于内存管理的内存原理。
二级存储(secondary storage)
对于计算机来说，存储体系是分层级的。离CPU越近的地方速度愉快，但容量越小(如图1所示)。比如：传统的计算机存储体系结构离CPU由近到远依次是:CPU内的寄存器，一级缓存，二级缓存，内存，硬盘。但同时离CPU越远的存储系统都会比之前的存储系统大一个数量级。比如硬盘通常要比同时代的内存大一个数量级。
图2.SQL Server可控控制内存的选项
而对于具体的SQL Server如何使用内存，例如分配给执行计划缓存多少，分配给数据buffer多少，这些都无法通过配置进行调控。这也是很多其它技术的开发人员对于使用微软技术的开发人员充满优越感的原因，而在我看来，虽然SQL Server提供可控配置的地方很少，但是很多地方都可以在通晓原理的情况下进行“间接”的配置。这也需要了解一些Windows的原理。
SQL Server是如何使用内存的
SQL Server存储引擎本身是一个Windows下的进程，所以SQL Server使用内存和其它Windows进程一样，都需要向Windows申请内存。从Windows申请到内存之后，SQL Server使用内存粗略可以分为两部分：缓冲池内存（数据页和空闲页）,非缓冲内存(线程，DLL,链接服务器等)。而缓冲池内存占据了SQL Server的大部分内存使用。缓冲池所占内存也就是图2最大最小内存所设置的，因此sqlservr.exe所占的内存有可能会大于图2中所设置的最大内存。
还有一点是，SQL Server使用内存的特点是：有多少用多少，并且用了以后不释放(除非收到Windows内存压力的通知)。比如我所在公司的开发服务器，在几乎没有负载的时候来看内存使用，如图3所示。
SQL Server OS的三层内存分配
SQL Server OS对于内存的分配分为三个层级,依赖关系如图4所示。
图5.查看Memory Node信息
我们可以看出 ，按照申请内存大小分类，可以分为两部分
1.申请小于等于8KB为一个单位的内存，这些内存被用于缓存。(图5中的SinglePage Allocator)
2.申请大于8KB为一个单位的内存，这些内存称为Multi-Page（或MemToLeave）(图5中的MultiPage Allocator)
对于为什么叫MemToLeave,被称为MemToLeave的原因是由于SQL Server虽然大部分内存被用于缓冲区，但还需要一些连续的内存用于SQL CLR,linked server,backup buffer等操作，32位SQL Server在启动实例时会保留一部分连续的虚拟地址（VAS）用于进行MultiPage Allocator。具体保留多少可以用如下公式计算：
保留地址=((CPU核数量-4)+256)*0.5MB+256MB,通常在384MB左右。
Memory Clerk
让我们再来看Memory Clerk,Memory Clerk用于分配内存，用于将Allocate出去的内存进行分类，可以简单的进行如下语句,如图6所示.
图7.Buffer Pool的相关信息
在SQL Server实例启动时，Buffer Pool所保留的VAS地址空间取决于多个因素:包括实际的物理内存和SQL Server是32位或是64位（这个限制32位是4G，还要划一半给Windows和减去MemToLeave空间）,而对于实际上SQL Server所使用的物理内存，可以通过如下语句查看，如图8所示。
图9.可以近似的估算出sql server所占的内存
Memory Object
menory object本质上是一个堆,由Page Allocator进行分配，可以通过sys.dm_os_memory_objects这个DMV进行查看，这个DMV可以看到有一列 Page_Allocator_Address列，这列就是Memory Clerk的标识，表明这个Memory Object是由哪个Memory Clerk进行分配的。
32位SQL Server的内存瓶颈
由文章前面所述的一些基本原理可以看出，由于32位的SQL Server使用的是VAS进行地址分配，因此寻址空间被限制在4GB，这4GB还要有一半分给Windows,使得Buffer Pool最多只能用到2G的内存，这使得32位SQL Server即使有多余的物理内存，也无法使用。
解决办法之一是通过减少Windows默认占用的2G到1G，使得SQL Server可以使用的内存变为3G。这个可以通过在Windows Server 2008中的命令行键入 BCDEdit /set设置increaseuserva选项，设置值为3072MB，对于Windows Server 2003来说，需要在boot.ini中加上/3gb启动参数。
另一种办法是使用AWE（Address Window Extension）分配内存。AWE通过计算机物理地址扩展(Physical Address Extension PAE)，增加4位，使得32位的CPU寻址范围增加到2的36次方，也就是64GB。基本解决了寻址范围不够的问题。
VirtualAlloc和AllocateUserPhysicalPages
VirtualAlloc 和AllocateUserPhysicalPages是SQL Server向Windows申请内存所使用的方法。在默认情况下，SQL Server所需要的所有内存都会使用VirtualAlloc去Windows申请内存，这种申请是操作系统层面的，也就是直接对应的虚拟内存。这导致一个问题，所有通过VirtualAlloc分配的内存都可以在Windows面临内存压力时被置换到虚拟内存中。这会造成IO占用问题。
而使用AllocateUserPhysicalPages所申请的内存，直接和更底层的页表(Page Table)进行匹配，因此使用这个方法申请的内存不会被置换出内存。在32位SQL Server的情况下，通过开启AWE分配内存，buffer pool中的data cache部分将会使用这个函数，而MemToLeave部分和Buffer Pool中的另一部分内存（主要是执行计划缓存）依然通过VirtualAlloc进行内存分配。
因此在开启通过AWE分配内存之前，SQL Server首先需要对应的权限，否则就会在日志中报错，如图10所示。
图11.锁定内存页(Lock Page In Memory)
64位SQL Server的问题
64位Windows基本已经不存在上述的内存问题，但是依然要注意，在默认情况下，64位的SQL Server使用的依然是VirtualAlloc 进行内存分配，这意味着所有分配的内存都会在Windows面临压力时将页置换出去，这很可能造成抖动（Buffer Pool Churn）,这种情况也就是SQL Server Buffer Pool中的页不断的被交换进硬盘，造成大量的IO占用(可以通过sys.dm_exec_query_memory_grants这个DMV查看等待内存的查询),因此64位SQL Server将Buffer Pool中的Date Page通过AllocateUserPhysicalPages来进行内存分配就能避免这个问题。与32位SQL Server不同的是，64位SQL Server并不需要开启AWE,只需开启如图11所示的“Lock Page In Memory”就行了。
但这又暴漏出了另一个问题，因为SQL Server锁定了内存页，当Windows内存告急时，SQL Server就不能对Windows的内存告急做出响应（当然了Buffer Pool中的非data cache和MemToLeave部分依然可以，但往往不够，因为这部分内存相比Data Cache消耗很小），因为SQL Server的特性是内存有多少用多少，因此很有可能在无法做出对Windows低内存的响应时造成Windows的不稳定甚至崩溃。因此开启了”Lock Page In Memory”之后，要限制SQL Server Buffer Pool的内存使用，前面图2中已经说了，这里就不再细说了。
还有一个问题是当Buffer Pool通过AllocateUserPhysicalPages分配内存时，我们在任务管理器中看到的sqlservr.exe占用的内存就仅仅包含 Buffer Pool中非Data Cache部分和MemToLeave部分，而不包含Data Cache部分，因此看起来有可能造成sqlservr.exe只占用了几百兆内存而内存的使用是几十G。这时我们就需要在Perfmon.exe中查看 SQL Server:Memory Manager\Total Server Memory计数器去找到SQL Server真实占用的内存。
总结
本文讲述了SQL Server对内存管理的基本原理和SQL Server对内存使用所分的部分，对于SQL Server性能调优来说，理解内存的使用是非常关键的一部分，很多IO问题都有可能是内存所引起的。
原文链接：http://www.cnblogs.com/CareySon/archive/2012/08/16/HowSQLServerManageMemory.html
