查看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_nameFROM sys.dm_os_buffer_descriptorsGROUP BY db_name(database_id) ,database_idORDER 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.nameFROM 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_idinner 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_idORDER 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_nameFROM sys.dm_os_buffer_descriptors aGROUP BY db_name(database_id) ,database_idORDER BY cached_pages_kb DESC;

来源 ：http://blogs.msdn.com/b/apgcdsd/archive/2011/01/11/buffer-pool.aspx

----添加对临时表的查看

--4查看tempdb在缓冲池中，占用大于1MB的临时表对象名称use tempdbgoSELECT t.dbName, t.TableName, t.cachedPageCt, t.MbFROM(        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' )