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.