Finding out the most cached database in the buffer cache - The DMV Way

,

While there are many ways and criteria to find out what database is the most used, under optimized, trouble maker etc....., here is one more to find out the database/s using the most buffer cache.

SELECT 
CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS database_name,
COUNT(*)AS cached_pages_count,
COUNT(*) / 128 / 1024 AS cache_size_gb

FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,database_id
ORDER BY cached_pages_count DESC;

Here is the results I got on one of the production SQL Servers. 

Lucky for me, it turned out to be an extreme case. Now I knew where I should focus optimization efforts or even if that database belongs with rest of the databases on that same server.

I have tested this query for SQL 2008 (SP3) and up.

Hope you find this useful.

Original post (opens in new tab)

Rate

Share

Share

Rate