文档章节

How Memory is being used

Jasper
 Jasper
发布于 2015/11/10 10:57
字数 553
阅读 6
收藏 0

To determine what plans are in the cache and how often they're used we can use sys.dm_os_memory_cache_counters dm view .

 

SELECT  TOP 6
 LEFT([name], 20) as [name],
 LEFT([type], 20) as [type],
 [single_pages_kb] + [multi_pages_kb] AS cache_kb,
 [entries_count]
FROM sys.dm_os_memory_cache_counters 
order by single_pages_kb + multi_pages_kb DESC

 here :

CACHESTORE_OBJCP are compiled plans for stored procedures, functions and triggers. 
CACHESTORE_SQLCP are cached SQL statements or batches that aren't in stored procedures, functions and triggers.  This includes any dynamic SQL or raw SELECT statements sent to the server. 
CACHESTORE_PHDR  These are algebrizer trees for views, constraints and defaults.  An algebrizer tree is the parsed SQL text that resolves the table and column names.

(you will find these counters in DBCC Memorystatus as well.Infact DBCC Memory Status uses this dm)

Generally you will find that CACHESTORE_SQLCP > CACHESTORE_OBJCP , but if the ratio of one to another is very high then we can say that there are more adhoc plans being run then Stored procedures.
That is the reason the sal statements are going in to  Plan cache.

You can also monitor the number of data pages in the plan cache using Performance Monitor (PerfMon) using SQLServer:Plan Cache object with the Cache Pages counter.  There are instances for SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) and Bound Trees (CACHESTORE_PHDR). This will give you the same picture ..for e.g. under bound tree : multiply cache pages by 8. you will get the same output as in dbcc memorystatus and the dm we used above.

After this to know the querry we can use sys.dm_exec_cached_plans and sys.dm_exec_sql_text dm views to find the queries

select  TOP 100
 objtype,
    usecounts, 
 p.size_in_bytes/1024 'IN KB',
 LEFT([sql].[text], 100) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts DESC

 Now , SQL Server memory is primarily used to store data (buffer) and query plans (cache). 

We will try to find what tables and indexes are in the buffer memory of your server you can use sys.dm_os_buffer_descriptors DMV.

Further , the query below can give us total currrent size of buffer pool .

select count(*) AS Buffered_Page_Count
 ,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors

 After we have found the Bufferpool size , we can see which database is using more memory by runnig the query below .

SELECT LEFT(CASE database_id 
   WHEN 32767 THEN 'ResourceDb' 
   ELSE db_name(database_id) 
        END, 20) AS Database_Name,
 count(*)AS Buffered_Page_Count, 
 count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC

 And then we can go further at object level to see what all objects are consuming memory (and how much) .We can use the query below in each database we wish to :

SELECT TOP 25 
 obj.[name],
 i.[name],
 i.[type_desc],
 count(*)AS Buffered_Page_Count ,
 count(*) * 8192 / (1024 * 1024) as Buffer_MB
    -- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC

 

© 著作权归作者所有

Jasper
粉丝 0
博文 6
码字总数 5658
作品 0
杭州
数据库管理员
私信 提问
redis - info memory 内存信息解析

  前言   redis 提供info这个命令来查看redis运行的信息。一般比较关注的是内存信息,可以通过info memory来查看内存使用情况。      memory模块   使用redis-cli连接redis-serve...

linux运维菜
2018/08/29
0
0
Redis info

命令:info [section] 通过给定可选的参数 section ,可以让命令只返回某一部分的信息: 1)info server 一般 Redis 服务器信息,包含以下域: server : redis_version : Redis 服务器版本 ...

learn_more
2016/09/30
33
0
Redis的内存和主从问题

Redis主从配置,没有使用VM和持久化。 现在查询master和slave,发现两个问题: 1、master的used_memory比used_memory_rss要小,但是内存使用率远远达不到系统内存和配置的最大内存(只用了几...

飞天虎
2013/07/19
1K
0
Erlang中数据类型的内存分布

The amount returned by erlang:memory/0-1 is the amount of memory actively allocated, where Erlang terms are laid in memory; this amount does not represent the amount of memory t......

学习在线
2014/02/22
114
0
如何查看redis内存使用情况

如何查看redis内存使用情况 Redis 是一个开源(BSD许可)的,内存中的数据结构存储系统,它可以用作数据库、缓存和消息中间件。Redis的基本特点之一内存数据库,因为其数据是放在内存中的所以...

arvin_qin
2017/05/26
0
0

没有更多内容

加载失败,请刷新页面

加载更多

一次看懂 Https 证书认证

TLS > 传输层安全性协定 TLS(Transport Layer Security),及其前身安全套接层 SSL(Secure Sockets Layer)是一种安全协议,目的是为网际网路通信,提供安全及数据完整性保障。 如图,TLS...

极客收藏夹
25分钟前
3
0
https证书买哪家好?有哪些供应商

在选购https证书前除了要了解类型外,还需要了解https证书供应商,毕竟不同的供应商,提供的产品质量与服务也是有差异的。今天小编就为大家讲讲https证书供应商方面的内容,希望各位会喜欢。...

安信证书
26分钟前
4
0
Zuul 配置

概述:zuul底层是基于servlet,是由一系列的filter链构成。 1、路由配置 a、单例serverId映射 zuul: routes: client-a: path: /client/** serviceId: client-a 意思是...

java框架开发者
44分钟前
3
0
zk中FinalRequestProcessor解析

是处理器最后一个环节 FinalRequestProcessor implements RequestProcessor 处理器链最后一个环节处理事务和非事务请求最后一个环节 构造器 public FinalRequestProcessor(ZooKeeperServer z...

writeademo
44分钟前
3
0
Axios 详解

首先祝广大程序猿们节日快乐! 一、axios简介 基于promise,用于浏览器和node.js的http客户端 二、特点 支持浏览器和 node.js 支持 promise 能拦截请求和响应 能转换请求和响应数据 能取消请求...

张兴华ZHero
45分钟前
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部