查看SQLServer各种缓存的情况

ytop

页面缓存：

SELECT * FROM sys.dm_os_buffer_descriptors
执行计划缓存：

SELECT * FROM sys.dm_exec_cached_plans
综合应用：

复制代码
SELECT count(*) AS cached_pages_count, obj.name, index_id, i.name AS IndexName
FROM sys.dm_os_buffer_descriptors AS bd
    INNER JOIN
    (
        SELECT object_id, object_name(object_id) AS name
            ,index_id ,allocation_unit_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_id, object_name(object_id) AS name   
            ,index_id, allocation_unit_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 sysindexes i ON obj.object_id = i.id AND obj.index_id = i.indid
WHERE database_id = db_id()
GROUP BY obj.name, index_id, i.name
ORDER BY cached_pages_count DESC;
复制代码