sqlserver|SqlServer 内存篇(三)—— SqlServer内存使用状况分析

既然sqlserver内存有那么多种,每种都可能有各自上限值,DBA也必须能够看到sqlserver每种内存到底使用了多少,究竟是哪一种接近了上限、是哪部分内存不足,才能更好地解决问题。
通常可以用两种方法看到各部分内存用量——内存相关计数器和DMV视图

一、 内存相关计数器 与sqlserver相关的计数器通常以SQLServer:或MSSQL&开头,与内存相关的主要如下:

1. SQLServer:Memory Manager 总体内存使用情况

  • Total Server Memory(KB):sqlserver buffer pool当前大小
  • Target Server Memory(KB):SqlServer理论上能使用的最大内存数,min(AWE,"Max Server Memory",当前服务器可用内存数)
sqlserver|SqlServer 内存篇(三)—— SqlServer内存使用状况分析
文章图片

下面是sqlserver各部分内存使用情况:
  • Optimizer Memory(KB):正在用于查询优化的状态内存数
  • SQL Cache Memory(KB):正在用于动态sqlserver高速缓存的内存数
  • Lock Memory(KB):用于锁的内存总量
  • Connection Memory(KB):正在用于维护连接的内存数
  • Granted Workspace Memory(KB):正在用于哈希、排序、索引创建等操作的内存数
  • Memory Grants Pending:等待工作空间内存授权的进程总数,大于0说明用户内存由于内存压力而被延迟。一般来说,意味着有较严重的内存瓶颈

2. SQLServer:Buffer Manager 数据页读写情况
buffer pool是sqlserver内存使用最多也最容易出现瓶颈的部分,因此这部分计数器非常重要。
sqlserver|SqlServer 内存篇(三)—— SqlServer内存使用状况分析
文章图片

  • Buffer Cache Hit Ratio:缓存命中率,对于OLTP,如果低于95%,可能内存不足
  • Database pages:缓冲池中数据库页数,即database cache的大小
  • Free pages:总空闲页数,正常应该在降低到一定程度后维持稳定。如果这个值过低或反复降低,可能内存不足
  • Lazy writes/sec:Lazy writer每秒写出的缓冲区数,当sqlserver感觉到内存压力时,Lazy writer会将最久未用到的内存刷到磁盘以清理内存。如果这个值很大,或者Lazy writer经常被调用,可能内存不足
  • Page Life Expectancy:页若不被引用,将在缓冲池停留的秒数。当sqlserver感觉到内存压力,Lazy writer被触发,PLE的值也会突然下降。如果PLE的值很小或者总是高高低低,不能维持在稳定水平,可能内存不足
  • Page reads/sec:每秒发出的物理数据库页读取数
  • Stolen pages:用于非database pages(包括执行计划缓存)的页数,这就是stolen memory在buffer pool中的大小
  • Targetpages:缓存池目标页数,乘以8KB就是Target Server Memory的值
  • Total pages:缓存池当前页数,乘以8KB就是Total Server Memory的值
【sqlserver|SqlServer 内存篇(三)—— SqlServer内存使用状况分析】
二、 动态性能视图DMV sqlserver使用Memory Clerk方式统一管理内存分配和回收,而跟踪内存使用最常用的视图也就叫做。

1. sqlserver各部分内存使用情况 —— sys.dm_os_memory_clerks
selecttype , sum(virtual_memory_reserved_kb) VM_Reserved , sum(virtual_memory_committed_kb) VM_Commited , sum(awe_allocated_kb) AWE_Allocated , sum(shared_memory_reserved_kb) Shared_Reserved , sum(shared_memory_committed_kb) Shared_Commited --, sum(single_pages_kb)--SQL2005、2008 --, sum(multi_pages_kb)--SQL2005、2008 fromsys.dm_os_memory_clerks group by type order by type;

字段含义如下:
  • type:Memory Clerk名称,根据名称可以大概知道用途
  • virtual_memory_reserved_kb:该Clerk reserve的虚拟内存量
  • virtual_memory_committed_kb:该Clerk commit的虚拟内存量,提交的内存应始终小于保留的内存
  • awe_allocated_kb:该Clerk 使用AWE分配的内存量,主要用于2005、2008版本
  • shared_memory_reserved_kb:该Clerk reserve以供共享内存及文件映射使用的内存量
  • shared_memory_committed_kb:该Clerk commit以供共享内存及文件映射使用的内存量,这两个字段可以追踪shared memory的大小
  • single_pages_kb:通过stolen分配的单页内存量,主要用于2005、2008版本
  • multi_pages_kb:分配的多页内存量,主要用于2005、2008版本
sqlserver|SqlServer 内存篇(三)—— SqlServer内存使用状况分析
文章图片


主要type如下:
  • 数据页面缓存
sqlserver|SqlServer 内存篇(三)—— SqlServer内存使用状况分析
文章图片

sqlserver|SqlServer 内存篇(三)—— SqlServer内存使用状况分析
文章图片

  • 其他对象缓存
sqlserver|SqlServer 内存篇(三)—— SqlServer内存使用状况分析
文章图片

  • 信息缓存
sqlserver|SqlServer 内存篇(三)—— SqlServer内存使用状况分析
文章图片

  • 其他sqlserver功能组件(consumer)
sqlserver|SqlServer 内存篇(三)—— SqlServer内存使用状况分析
文章图片


2. sqlserver缓存了哪些对象 —— sys.dm_os_buffer_descriptors
-- 查询当前数据库缓存的所有数据页面,哪些数据表,缓存的数据页面数量 -- 从这些信息可以看出,系统经常要访问的都是哪些表,有多大? select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=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 p.object_id,p.index_id order by buffer_pages desc;


3. 执行计划都缓存了什么 —— sys.dm_exec_cached_plans
sqlserver|SqlServer 内存篇(三)—— SqlServer内存使用状况分析
文章图片

  • 查询执行计划中各种对象各占多少内存
-- 查询缓存的各类执行计划,及分别占了多少内存 -- 可以对比动态查询与参数化SQL(预定义语句)的缓存量 selectcacheobjtype , objtype , sum(cast(size_in_bytes as bigint))/1024 as size_in_kb , count(bucketid) as cache_count fromsys.dm_exec_cached_plans group by cacheobjtype, objtype order by cacheobjtype, objtype

  • 查具体存储了哪些sql
查询结果会很大,注意将结果集输出到表或文件中,直接输出到DB服务器的SMSS可能导致资源争用
-- 查询缓存中具体的执行计划,及对应的SQL -- 将此结果按照数据表或SQL进行统计,可以作为基线,调整索引时考虑 SELECTusecounts , refcounts , size_in_bytes , cacheobjtype , objtype , TEXT FROMsys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) ORDER BY objtype DESC ;

    推荐阅读