Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

migrated from stackoverflow.com May 6 '12 at 18:00

This question came from our site for professional and enthusiast programmers.

1 Answer

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.

share|improve this answer
    
Thanks this will be really helpful to me –  user1197981 May 5 '12 at 13:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.