.

/*

 SQL Server 2008 and R2 Diagnostic Information Queries
 Glenn Berry
 June 2012
 Last Modified: June 19, 2012
 http://sqlserverperformance.wordpress.com/
 http://sqlskills.com/blogs/glenn/
 Twitter: GlennAlanBerry

DESCRIPTION:  List CPU usage for of all files for all databases

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000
 Get total buffer usage by database for current instance  (Query 18)
 This make take some time to run on a busy instance

Update Log:

*/

select
DB_NAME(database_id) AS [Database Name]
, COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
from sys.dm_os_buffer_descriptors WITH (NOLOCK)
where database_id > 4 -- system databases
and database_id <> 32767 -- ResourceDB
group by DB_NAME(database_id)
order by [Cached Size (MB)] DESC OPTION (RECOMPILE);
 
/*
Tells you how much memory (in the buffer pool)
is being used by each database on the instance
*/

.