七、内存优化(3)使用DMV

2023-01-31 01:01:05 内存优化 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查看的消耗情况,以及缓存的信息。


相关文章