查看buffer pool使用

--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' )