七、内存优化（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查看的消耗情况，以及缓存的信息。
相关文章