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 retreived 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 retreived to the user/application.
Database Pages which occupied in the cache are retreived 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
Hope the above information helps
Thanks
