一些内存使用错误理解

在实际的场景中会遇到各种奇怪的问题，为什么会感觉到奇怪，因为没有理论支撑的东西才感觉到奇怪，SQL Server自己管理内存，我们可以干预的方式也很少，所以日常很难遇到处理内存问题的案例。当遇到了原有的知识储备已经变得模糊，这是已经记不住第几遍阅读《SQL 2012实施与管理实战指南》内存管理章节，也分享给群友。

一些内存使用错误理解

    本文来澄清一些用户经常对SQL Server内存使用的误解。对这些知识的理解可以帮助数据库管理员理解SQL Server的行为 

    1. Windows上还有很多物理内存没有被使用，就意味着SQL Server不缺内存

    这个观点是非常错误的，因为:

    (1)SQL Server很可能设置了Max Server Memory，约束了SQL Server继续申请内存的能力。（注：实际场景中就遇到过客户128GB内存的机器SQL Server 的Max Server Memory 被设置成2048MB 导致严重的问题）

    (2)在32位的机器上 ,由于虚拟地址空间的限制，SQL Server可能已经无法继续申请内存。

    例如，在一台4 GB或更大的32位服务器上，SQL Server最多只能使用2 GB物理内存。一般Windows会使用500 MB左右的物理内存，这台机器可能就有1 GB多的空闲物理内存。这部分内存是SQL Server不开AWE就用不到的。 所以，服务器上有很多空闲物理内存，只能说明Windows不缺内存，不能说明SQL Server自己不缺内存。（这也是为什么建议更换老一代服务器，使用64位的操作系统和SQL Server的原因）

    2.  SQL Server进程的Private Bytes(或Working Set)在不停地向上涨，说明SQL Server有内存泄漏的问题

    恰恰相反，对于申请内存，SQL Server有严格的限制。在32位的服务器上，不管是Buffer Pool还是MemToLeave，都有一个使用最大上限。当Windows感觉到有内存压力的时候，SQL Server又会主动地释放内存所以作为一个进程，SQL Serve发生内存泄漏的机会是非常小的。

    之所以会有这样的疑惑，是因为SQL Server启动的时候，仅会Commit它启动所需要的那部分内存。随着用户的使用，SQL Server会不断地申请内存，直到Windows感觉到内存压力，或者SQL到了自己的上限为止,在这之前，SQL Server的内存使用量的确会不断地增加。对SQL Server来讲，缓存越多的数据，它的性能会越好这种增长是正常的，不用焦虑如果不希望SQL Server使用那么多内存，只需设置Max Server Memory这个上限即可。

　　（注：实际情况中很多人提到过，SQL Server竟然吃掉了所有服务器的内存，是不是内存泄露了？或问内存是不是这就是我系统的瓶颈呀？） 

    3.  Max Server Memory的值，就是SQL Server内存使用量的最大值。超过这个值就不正常

　　Max Server Memory这个值应该是Buffer Pool的上限(此点针对SQL Server 2005/2008而言，对于SQL Server 2012内存管理发生了非常大的变化)，而不是SQL Server所有内存使用的上限。由于SQL Server 内存的使用包括Buffer Pool和MemToLeave，所以SQL Server实际内存使用量一定会比Max Server Memory要大但是在正常情况下SQL Server MemToLeave的使用会远小于Buffer Pool的使用，控制好Buffer Pool，就基木控制住了SQL Server的整体内存使用量

 　　（注：建议无论内存是否存在压力都要合理的设置最大内存，PS：我也曾经被使用的内存超过设置的Max Server Memory吓了一跳）

    4. SQL Server的内存使用总量，就是性能监视器里面的SQL Server:Memory Manager一Total Server Memory的值

    性能监视器里面与SQL Server相关的counter，都是SQL Server自己负责收集的。从SQL Server 2005以后，SQL整合了所有的内存申请，让它们使用同一的接口。所以SQL Server对自己申请的内存数量，是了如指掌的，但问题是，在SQL Server进程里运行的代码不都是SQL Server自己的代码。对第三方的代码，SQL Server是不知道它们申请了多少内存的。

    SQLServer:Memory Manager - Total Server Memory的值，是SQL Server自己的代码申请的内存空间大小。真正SQL Server进程申请的空间值，会比这个值大一些。(具体大多少和MemToLeave的大小有关系)

    如果SQL Server没有开启AWE,SQL Server进程申请的逻辑内存数和物理内存数可以由Process下的Private Bytes和Working Set看出。这两个值会包含所有的内存支出，包括SQL自己的代码和第三方的代码。

    如果SQL Server开启了AWE，问题就比较尴尬了。因为Windows没有办法正确判断出一个使用了AWE 内存的进程，究竟总共用了多少内存。我们只能借助SQLServer:Memory Manager一Total Server Memory来判断SQL Server的Buffer Pool使用量。至于SQL Server自己申请的内存总数(Buffer Pool + MemToLeave )，可以通过查询和内存相关的DMV计算出来，但第三方的代码申请的内存，就很难做精确计算了

    5.当系统有内存压力的时候，SQL Server总是会自动释放内存

    默认情况下，SQL Server的确会在系统有内存压力的时候自动释放内存但是有个例外:SQL Server启动时会试图做“Lock Page In Memory”的动作。如果启动账号有这个权限，动作就会成功。那么当同一台服务器上的其他应用程序需要内存的时候，SQL Server很可能不会释放内存。所以在这种情况下，建议SQL Server设置Max Server Memory上限。

　　（注：Lock Page In Memory很多资料上写到SQL的内存不会被释放了，但实际情况中，当操作系统感觉到压力一样会把SQL的内存释放掉，也是错误理解6的由来） 

    6.  SQL Server有办法将自己的内存绑定在物理内存里

    SQL Server的确想通过Lock Page In Memory的方法达到这个目的。但是，作为一个用户态为主的应用程序，它还是会受限于核心态。如果核心态里发出内存要求，SQL Server就会被迫把自己的内存释放出来。

    7.增加MemToLeave的大小可以提高SQL Server的性能

    在32位的SQL Server上，默认MemToLeave是256 MB+0.5 MB x ( Max Thread数目)。如果MemToLeave 用完了，SQL Server的一些重要功能就不能进行，甚至新的连接都建立不起来所以一些对MemToLeave需求比较大的SQL Server，例如，一些经常运行Linked Server分布式查询的SQL Server，或者是一些运行CLR,Extended Stored Procedur的SQL Server，可能不得不再加一些MemToLeave空间。这可以使用SQL Server的

一个启动参数一g完成。例如，如果想把MemToLeave设成512 MB+0.5 MB x ( Max Thread数目)，可以加启动参数一g512。

    但是需要想清楚的是，SQL Server的虚拟地址空间只有2 GB，给MemToLeave越多，Buffer Pool能拿到的就越少。Data Cache的内存还可以从AWE扩展的内存补，Buffer Pool里的Stolen内存就没办法了。所以其实这是拆东墙补西墙，关键要看哪一块内存对SQL Server的性能和稳定运行最重要。不必要多给，这会浪费资源，影响Buffer Pool里面的性能。只有确定了SQL Server的MemToLeave的确不够，才应该去增大它

　8.增加物理内存一定会提高SQL Server的性能

    既然SQL Server这么喜欢内存，那管理员多给服务器配备一些内存，是不是就一定能够提高性能呢?很多时候这个想法是对的，但是并不是总是正确。这是因为增加的内存SQL Server不一定用得到。

    (1)首先要检查是哪一部分的内存有瓶颈，是Database Cache，还是Stolen，是Buffer Pool，还是MemToLeave。

    (2)要检查缺的那部分内存是不是已经到了理论上的上限，新加内存SQL Server用不用得上例如，在32位+AWE的服务器上，Buffer Pool里的Stolen Memory最多也不过1.6 GB。如果是这一部分内存不够，再加内存也没有用。只有将系统升级到64位，才能突破这些限制。

    (3)加人的内存，一般大部分都会被Buffer Pool使用，这样SQL Server就会缓存更多的数据页面和执行计划，大多数时候这会对性能有帮助。如果Database Pages没有压力，SQL Server会缓存太多的动态T-SQL执行计划，对性能没什么好处，反而会增加SQL Server的维护成本。只有在SQL Server的Database Page缺内存的时候，增加内存才会对性能有明显帮助。如果数据库比较小，常用数据页面已经缓存在了内存里，增加内存对性能不会有太大帮助。

　　（注：内存这个东西对于一些初级DBA分析起来还是有些难度，而很多情况下只要看到内存压力就要添加内存这样也是不对的，很多时候内存的消耗很自己程序的语句优化程度也有着非常大的关系） 

　9. Stolen的内存真的是偷来的吗

   很多在描述Stolen的内存块的时候都说这块内存是不需要保留(Reserve)而直接提交(Commit )使用的，所以这块内存叫作Stolen。这么说错了吗?其实，从Windows的层面，对于任何内存的使用，都必须遵循先Reserve再Commit的过程。对于一块内存，如果不Reserve而直接使用会导致访问违例(Access Violation)因为SQL Server内部的内存管理机制，所有要使用的内存，Buffer Pool都已经帮我们保留(Reserve)好了如果SQL所要做的，是用Buffer Pool已经保留(Reserve)好了的那部分地址空间去直接提交(Commit )，而且，这一部分内存又不是用来存放Buffer的，就被称为Stolen