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?? – Remus Rusanu 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.

| improve this answer | |

Your Answer

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