--1.Buffer Pool的内存主要是由那个数据库占用 :
SELECT cast(count(row_count)*8.0/1024.0 as decimal(12,2)) as cached_pages_mb,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_kb DESC;
--2.当前数据库的哪个表或者索引占用Pool缓冲空间最多
SELECT count(*)*8 AS cached_pages_kb,schema_name(sobjs.schema_id)
,obj.name ,obj.index_id,b.type_desc,b.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.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes b on b.object_id = obj.object_id AND b.index_id =
obj.index_id
inner join sys.objects as sobjs on obj.object_id=sobjs.object_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id ,b.name,b.type_desc,sobjs.schema_id
ORDER BY cached_pages_kb DESC;
--3. Buffer Pool缓冲池里面修改过的页总数大小:
SELECT count(*)*8 as cached_pages_kb,
convert(varchar(5),convert(decimal(5,2),(100-1.0*(select count(*) from
sys.dm_os_buffer_descriptors b where b.database_id=a.database_id and
is_modified=0)/count(*)*100.0)))+'%' modified_percentage
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors a
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_kb DESC;
来源 :http://blogs.msdn.com/b/apgcdsd/archive/2011/01/11/buffer-pool.aspx
----添加对临时表的查看
--4查看tempdb在缓冲池中,占用大于1MB的临时表对象名称
use tempdb
go
SELECT t.dbName, t.TableName, t.cachedPageCt, t.Mb
FROM(
SELECT CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(a.database_id)
END AS dbName,OBJECT_NAME(c.object_id) TableName,COUNT(*) cachedPageCt,COUNT(*)*8/1024.0 Mb
from sys.dm_os_buffer_descriptors a
left join sys.allocation_units b ON a.allocation_unit_id=b.allocation_unit_id
left join sys.partitions c ON b.container_id=c.hobt_id
WHERE 1=1
group by a.database_id,c.object_id having COUNT(*)*8/1024.0>1
)t
WHERE (t.dbName ='tempdb' )