Memory utilization per database - SQL Server

Question

I am trying to find the memory utilization per database(using dmvs) in a sql server instance, ie (Buffer Cache + Procedure Cache) over a period of time. I have the query to get the buffer cache utilization, what i require is procedure cache, plus any other parameter that a database will use in terms of memory.

-- Get total buffer usage by database for current instance
SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
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);

Also if this can be compared with some perfmon counter info, it would be great

• hkf: Depends on your server configuration. Too localised + not a real question.
• user1197981: sorry, I should have been more clear - I meant how can it be found out programatically, using dmvs.. thank you
• Remus Rusanu: Why would you add the OPTION(RECOMPILE) to this query??

Answer 1

SELECT db = DB_NAME(t.dbid), plan_cache_kb = SUM(size_in_bytes/1024) 
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.dbid < 32767
GROUP BY t.dbid
ORDER BY plan_cache_kb DESC;

This tip I wrote on mssqltips.com might be useful as well if you want to dig deeper into buffer usage.