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.
WHEN 32767 THEN 'ResourceDb'
END AS database_name,
COUNT(*) / 128 / 1024 AS cache_size_gb
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.