sql server实例内存使用统计

转载于： http://blog.csdn.net/shutao917/article/details/51444424

SQL SERVER内存按存放数据的类型，大概可以分为三类：

1、buffer pool，存放数据页面的缓冲区，sql server数据都是存放在一个个8K的页面里，当用户需要使用这个页面上的数据时，都是把整个页面加载到内存的buffer pool区缓存起来。

2、各类consumer:

      connect:SQL SERVER为每一个客户端连接分配一块内存，用来存储连接的信息，以及发过来的指令和缓存指令结果待待客户端取走

      无数据：表、存储过程、索引等的元数据

      锁：SQL SERVER中锁是稀有资源，会占用大量内存

      Query plan:缓存SQL的执行计划

      Optimizer:生成执行 计划过程中需要使用内存

3、线程内存：sql server会为每个线程分配0.5M的内存，用来存放线程的数据结构和相关信息

 下面的语句可以统计服务器实例的内存使用情况：

 select type,                           

• SUM(single_pages_kb)/1024 single_pages_mb,                           
• SUM(multi_pages_kb)/1024 multi_pages_mb,                            
• SUM(virtual_memory_reserved_kb)/1024 virtual_memory_reserved_mb,                            
• SUM(virtual_memory_committed_kb)/1024 virtual_memory_committed_mb,                            
• sum(shared_memory_committed_kb)/1024 shared_memory_committed_mb                            
• from sys.dm_os_memory_clerks                            
• group by type                                               
• order by 1;

 

结果如下：

CACHESTORE_OBJCP：存储过程、函数等的执行计划

CACHESTORE_SQLCP：SQL语句的执行计划(这里SQL语句的执行计划占用了多达6G的内存，是因为程序存在大量没有绑定变量的SQL语句，下次再做介绍)

MEMORYCLERK_SQLBUFFERPOOL：Buffer pool

OBJECTSTORE_LOCK_MANAGER：锁

下面再来看下BUFFER POOL中，都缓存了哪些表(当前数据库)的数据：

  select OBJECT_NAME(object_id) 表名,COUNT(*) 页数,COUNT(*)*8/1024.0 Mb                              

from   sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c                              

where  a.allocation_unit_id=b.allocation_unit_id   

and b.container_id=c.hobt_id             

 and database_id=DB_ID()                              

group by OBJECT_NAME(object_id)                           

order by 2 desc  

方法二：使用以下语句查找出什么语句读 内存很高，查处查询语句或过程在进行优化

SELECT SS.SUM_EXECUTION_COUNT,        T.TEXT,        SS.SUM_TOTAL_ELAPSED_TIME,        SS.SUM_TOTAL_WORKER_TIME,        SS.SUM_TOTAL_LOGICAL_READS,        SS.SUM_TOTAL_LOGICAL_WRITES FROM (SELECTS.PLAN_HANDLE,              SUM(S.EXECUTION_COUNT)SUM_EXECUTION_COUNT,              SUM(S.TOTAL_ELAPSED_TIME)SUM_TOTAL_ELAPSED_TIME,              SUM(S.TOTAL_WORKER_TIME)SUM_TOTAL_WORKER_TIME,              SUM(S.TOTAL_LOGICAL_READS)SUM_TOTAL_LOGICAL_READS,              SUM(S.TOTAL_LOGICAL_WRITES)SUM_TOTAL_LOGICAL_WRITES        FROM SYS.DM_EXEC_QUERY_STATSS        GROUP BY S.PLAN_HANDLE       ) AS SS       CROSS APPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T ORDER BY SUM_TOTAL_LOGICAL_READS DESC