SQL Server内部的内存管理

    1、内存使用分类

    按用途分类

    1）Database cache（数据页面）。SQL Server中的页面都是以8KB为一个页面存储的。当SQL Server需要用到某个页面时，它会将该页面读到内存中，使用完后会缓存在内存中。在内存没有压力的情况下，SQL Server不会将页面从内存中删除。如果SQL Server感觉到内存的压力时，会将最长时间没有使用的页面从内存中删除来空出内存。

    2）各类Consumer（功能组件）

    Connection的连接信息

    General：一组大杂烩。语句的编译、范式化、每个锁数据结构、事务上下文、表格和索引的元数据等

    Query Plan：语句和存储过程的执行计划。和Database cache类似，SQL Server也会将执行计划缓存以供将来使用，减少编译时间。

    Optimizer：生成执行计划的过程中消耗的内存。

    Utilities：像BCP、Log Manager、Backup等比较特殊的操作消耗的内存。

    3）线程内存：存放进程内每个线程的数据结构和相关信息消耗的内存，每个线程需0.5MB的内存。

    4）第三方代码消耗的内存：SQL Server的进程里，会运行一些非SQL Server自身的代码。例如：用户定义的CLR或Extended Stored PRocedure代码。

    按申请方式分类

    1）预先Reserve一块大的内存，然后在使用的时候一块一块的Commit。Database Page是按这种方式申请的。

    2）直接用Commit方式申请的内存，成为Stolen方式。除了Database Page之外其他内存基本都是按这种方式申请的。

    按申请内存的大小分类

    1）申请小于等于8KB为一个单位的内存，这些内存称为Buffer Pool

    2）申请大于8KB为一个单位的内存，这些内存称为Multi-Page（或MemToLeave）

    SQL Server对于Database cache都是采用先Reserved后Commit的方式申请的，而数据页都是以8KB为单位进行申请的。

    对于Consumer中的内存申请，一般都是按Stolen方式申请的，且大多数的执行计划的大小都是小于8KB的，少数特别复杂的存储过程的执行计划会超过8KB，默认的连接的数据包是4KB，除非客户端特别设置了超过8KB（不建议）

    第三方代码的内存申请一般是按Stolen方式申请的，个别比如CLR中可能会用Reserved/Commit的方式申请。

    线程的内存每个都以0.5MB的方式申请，自然是放在MemToLeave中。

    之所以花了这么大篇幅来讲SQL Server的内存分类，是因为SQL Server尤其是32位的SQL Server对不同种类的内存的申请大小是不一样的，对Commit、Stolen和MemTOLeave等类型的内存是有限制的。因此会出现系统中还有空闲内存，但是SQL Server不会申请使用的现象。

  2、SQL Server内存使用情况的分析

    一般来说有两种方式，第一种就是用来分析系统内存情况时使用的用性能计数器来分析，第二种是使用动态管理视图（DMV，只适用于SQL Server2005和2008）

    1）SQL Server性能计数器

    SQLServer：Memory Manager：Total Server Memory（KB）：SQL Server缓冲区提交的内存。不是SQL Server总的使用内存，只是Buffer Pool中的大小。

    SQLServer：Memory Manager：Target Server Memory（KB）：服务器可供SQL Server使用的内存量。一般是由SQL Server能访问到的内存量和SQL Server的sp_Configure配置中的Max Server Memory值中的较小值算得。

    SQLServer：Memory Manger：Memory Grants Pending：等待内存授权的进程总数。如果该值不为0，说明当前有用户的内存申请由于内存压力被延迟，这意味着比较严重的内存瓶颈。

    SQLServer：Buffer Manager：Buffer Cache Hit Ratio：数据从缓冲区中找到而不需要从硬盘中去取的百分比。SQL Server在运行一段时间后，该比率的变化应该很小，而且都应该在98%以上，如果在95%以下，说明有内存不足的问题。

    SQLServer：Buffer Manager：Lazy Writes/Sec：每秒钟被惰性编辑器（Lazy writer）写入的缓冲数。当SQL Server感觉到内存压力的时候，会将最久没有使用的数据页面和执行计划从缓冲池中清理掉，做这个动作的就是Lazy Writer。

    SQLServer：Buffer Manager：Page Life Expectancy：页面不被引用后，在缓冲池中停留的秒数。在内存没有压力的情况下，页面会一直待在缓冲池中，Page Life Expectancy会维持在一个比较高的值，如果有内存压力时，Page Life Expectancy会下降。所以如果Page Life Expectancy不能维持在一个值上，就代表SQLServer有内存瓶颈。

    SQLServer：Buffer Manager：Database Pages ：就是Database Cache的大小。

    SQLServer：Buffer Manager：Free Pages：SQL Server中空闲可用的大小。

    SQLServer：Buffer Manager：Stolen Pages：Buffer Pool中Stolen的大小。

    SQLServer：Buffer Manager：Total Pages： Buffer Pool的总大小（等于Database Pages+Free Pages+Stolen Pages）。该值乘以8KB，应该等于Memory Manager：Total Server Memory的值。

    从上面这些计数器中我们就能了解SQL Server的内存使用情况，结合前面说的系统层的计数器大概能看出是否存在内存瓶颈。

  2）内存动态管理视图

    在SQL Server 2005以后，SQL Server的内存管理是使用Memory Clerk的方式统一管理。所有的SQL Server的内存的申请或释放，都需要通过它们的Clerk，SQL Server也通过这些Clerk的协调来满足不同需求。通过查询这些DMV，可以得到比用性能计数器更加详细的内存使用情况。

    我们可以通过下面的查询语句来检测SQL Server的Clerk的内存使用情况。

    使用sys.dm_os_memory_clerks查看内存使用情况

    SELECT type, --Clerk的类型
    sum（virtual_memory_reserved_kb） as vm_Reserved_kb, -- 保留的内存
    sum（virtual_memory_committed_kb） as vm_Committed_kb, --提交的内存
    sum（awe_allocated_kb） as awe_Allocated_kb, -- 开启AWE后使用的内存
    sum（shared_memory_reserved_kb） as sm_Reserved_kb, -- 共享的保留内存
    sum（shared_memory_committed_kb） as sm_Committed_kb, -- 共享的提交内存
    sum（single_pages_kb） as SinlgePage_kb, --Buffer Pool中的Stolen的内存
    sum（multi_pages_kb） as Multipage_kb -- MemToLeave的内存
    FROM sys.dm_os_memory_clerks
    GROUP BY type
    ORDER BY type

    从上面的查询语句，我们可以算出前面提到的内存大小

    Reserved/Commit = sum（virtual_memory_reserved_kb） / sum（virtual_memory_committed_kb）
    Stolen = sum（single_pages_kb） + sum（multi_pages_kb）
    Buffer Pool = sum（virtual_memory_committed_kb） + sum（single_pages_kb）
    MemToLeave = sum（multi_pages_kb）

    通过上面的介绍我们可以知道SQL Server总体和各部分内存的使用情况，如果我想知道数据页的缓存中到底缓存了哪些数据，这些数据是属于哪个数据库的哪个表中的呢？执行计划又是缓存了哪些语句的执行计划呢？这也可以通过DMV查看的到。

    查看内存中的数据页面缓存的是哪个数据库的哪个表格的数据

    declare @name nvarchar（100）
    declare @cmd nvarchar（1000）
    declare dbnames cursor for
    select name from master.dbo.sysdatabases
    open dbnames
    fetch next from dbnames into @name
    while @@fetch_status = 0
    begin
    set @cmd = 'select b.database_id, db=db_name（b.database_id），p.object_id,p.index_id,buffer_count=count（*） from '

    --这里的object_id代表是SQL Server中的对象号，index_id代表是索引号，buffer_count代表的是页面数

    + @name + '.sys.allocation_units a, '
    + @name + '.sys.dm_os_buffer_descriptors b, ' + @name + '.sys.partitions p
    where a.allocation_unit_id = b.allocation_unit_id
    and a.container_id = p.hobt_id
    and b.database_id = db_id（‘'' + @name + ''’）
    group by b.database_id,p.object_id, p.index_id
    order by b.database_id, buffer_count desc'
    exec （@cmd）
    fetch next from dbnames into @name
    end
    close dbnames
    deallocate dbnames
    go

    -- 根据上面取出来的@object_id找出是哪个数据库的哪个表

  SELECT    s.name AS table_schema, o.name as table_name --使用的就是table_schema.table_name表
    FROM    sys.sysobjects AS o INNER JOIN
    sys.schemas AS s ON o.uid = s.schema_id
    WHERE    （o.id = @object_id）

    -- 根据上面取出来的@object_id和@index_id找出索引的名称

    SELECT    id, indid, name as index_name -- index_name就是索引的名称
    FROM    sys.sysindexes
    WHERE    （id = @object_id） AND （indid = @index_id）

    -- 根据上面取出来的表名table_schema.table_name和索引的名称index_name，还可以找出该索引是建立在哪些字段上的

    EXEC sp_helpindex 'table_schema.table_name'

    查看内存中缓存的执行计划，以及执行计划对应的语句：

    -- 输出可能较大，请小心使用

    SELECT   usecounts, refcounts, size_in_bytes, cacheobjtype, objtype, text
    FROM    sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text（plan_handle）
    ORDER BY objtype DESC