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)    

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

  • Depends on your server configuration. Too localised + not a real question.
    – hkf
    May 4 '12 at 2:16
  • sorry, I should have been more clear - I meant how can it be found out programatically, using dmvs.. thank you
    – user1197981
    May 4 '12 at 2:30
  • Why would you add the OPTION(RECOMPILE) to this query?? May 4 '12 at 4:30
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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy