Query to find space occupied by each database in Data cache

Every database in SQL Server which is getting read by the user or application is retrieved from the buffer cache. If the pages which are not in buffer cache are read by the user/application, those pages will be loaded into buffer cache first and then retrieved to the user/application.

 

Database Pages which occupied in the cache are retrieved by the following query:

 SELECT count(*)*8/1024 AS 'Cached Size (MB)', 
CASE database_id  WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)  END AS 'Database'  
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id 
ORDER BY 'Cached Size (MB)' DESC
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s