七、内存优化（3）使用DMV

一、sys.dm_os_memory_clerks

1. 查询DMV

sys.dm_os_memory_clerks返回SQL Server实例中当前处于活动状态的全部内存Clerk的集合。跟踪这个DMV，可以看到内存是如何被SQL Server消耗。

select type, sum(virtual_memory_reserved_kb) as virtual_memory_reserved_sum, sum(virtual_memory_committed_kb) as virtual_memory_committed_sum, sum(awe_allocated_kb) as awe_allocated_sum, sum(shared_memory_reserved_kb) as shared_memory_reserved_sum, sum(shared_memory_committed_kb) as shared_memory_committed_sum, sum(multi_pages_kb) as multi_pages_sum, sum(single_pages_kb) as single_pages_sum from sys.dm_os_memory_clerks group by type order by type

2. 各列的解释

（1）Type

　　Momery Clerk的名称。大致可以根据名称猜出内存的用途。

（2）virtual_memory_reserved_sum 和 virtual_memory_committed_sum

virtual_memory_reserved_sum 是内存Clerk Reserve的虚拟内存量。这是由使用此Clerk的组件直接保留的内存量。在多数情况下，只有使用Buffer Pool的内存Clerk才会有这种机制。

virtual_memory_committed_sum 是内存Clerk Commit的虚拟内存量。这是Clerk提交的内存量。提交的内存量应始终小于保留的内存量。这部分内存，主要来自Database Pages。

（3）awe_allocated_sum

　　内存Clerk使用AWE分配的内存量。

（4）shared_memory_reserved_sum 和 shared_memory_committed_sum

shared_memory_reserved_sum 是内存Clerk保留的共享内存量。保留以供共享内存和文件映射使用的内存量。

shared_memory_committed_sum 是内存Clerk提交的共享内存量。

　　这2个列的值，可以追踪Shared Memory的大小。一般Shared Memory的值都很小。

（5）multi_pages_sum 和 single_pages_sum

multi_pages_sum 是分配的多页内存量。此内存在缓冲池外面分配，也就是传统意义上SQL Server自己的代码使用的Multi-Page的大小。

　　single_pages_sum 是通过Stolen Page分配的单页内存量。也就是说，是Buffer Pool里的Stolen Memory的大小。

3. 估算不同内存的大小

（1）Reserved 和 Commit

　　即 sum(virtual_memory_reserved_kb) 和 sum(virtual_memory_committed_kb) 。

（2）Stolen

　　等于 sum(single_pages_kb) + sum(multi_pages_kb)

（3）Buffer Pool(Single Page）

　　等于 sum(virtual_memory_committed_kb) + sum(single_pages_kb)

（4）Multi-Page

　　即 sum(multi_pages_kb)

4. 局限性

　　通过这个DMV可以看到所有Buffer Pool(或者称为Single Page）的使用，以及Multi-Page里被SQL Server代码用掉的内存。运行在Multi-Page里面的第三方代码所申请的内存是不能被这个视图跟踪的。

二、查看SQL Server缓存的数据页面信息

　　通过sys.dm_os_buffer_descriptors，可以查看SQL Server缓冲池中当前所有数据页的信息。

use db01 select b.database_id ,db=DB_NAME(b.database_id) , p.object_id , p.index_id , buffer_count = COUNT(*) from sys.allocation_units a, sys.dm_os_buffer_descriptors b, 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() group by b.database_id ,p.object_id ,p.index_id order by b.database_id , buffer_count desc

三、查看SQL Server缓存的执行计划信息

　　可以通过sys.dm_exec_cached_plans查看执行计划都缓存了哪些内容。

select cacheobjtype,objtype from sys.dm_exec_cached_plans group by cacheobjtype,objtype

　　通常缓存的执行计划的对象有：Proc（存储过程）、Prepared（预定义语句）、Adhoc（动态查询）、ReplProc（复制筛选过程）、Trigger、View、Default（默认值）、UsrTab（用户表）、SysTab（系统表）、Check（Check约束）、Rule（规则）等。

　　通过下面的查询，可以看到各种对象占用了多少内存。

select cacheobjtype,objtype, sum(size_in_bytes) as sum_size_in_bytes, sum(bucketid) as cache_counts from sys.dm_exec_cached_plans group by cacheobjtype,objtype

本文结语：

　　通过DMV查看的消耗情况，以及缓存的信息。