;WITH BufPoolStats
AS
(
SELECT
database_id
,COUNT_BIG() AS page_count ,CONVERT(DECIMAL(16,3),COUNT_BIG() * 8 / 1024.) AS size_mb
,AVG(read_microsec) AS avg_read_microsec
FROM
sys.dm_os_buffer_descriptors WITH (NOLOCK)
GROUP BY
database_id
)
SELECT
DB_NAME(database_id) AS [DB]
,size_mb
,page_count
,avg_read_microsec
,CONVERT(DECIMAL(5,2), 100. * (size_mb / SUM(size_mb) OVER()))
AS [Percent]
FROM
BufPoolStats
ORDER BY
size_mb DESC
OPTION (MAXDOP 1, RECOMPILE);
Categories:
Tags:
No responses yet