Memory from Every Angle

It seems like questions always come up about how to monitor memory. But each time the question comes up, it’s slightly different. Here’s a sort of FAQ for memory questions: 1. How can I figure out memory and CPU consumption by database? a. In SQL Server 2005, DMVs are of course the way to go. Pre-SQL Server 2005, you will probably want to use PerfMon. b. Sys.dm_exec_query_stats with sys.dm_exec_sql_text will give per database object stats. Note that there can be ad-hoc statements executed against db which will be difficult to track c. Sys.dm_io_virtual_file_stats - File level IO stats per database file. You could track reads/writes this way d. Sys.dm_os_buffer_descriptors - Buffer pool information. You can derive this per database2. How do I find out memory consumption by worker thread? a. This is a tricky challenge because this memory is controlled by the kernel, not the buffer pool. The buffer pool does try to account for it as part of SQL Server’s set-aside which you sometimes override with -g. b. Once again, your best bet is the DMV sys.dm_os_threads. It has two columns that help with worker thread stacks: stack_bytes_committed and stack_bytes_used. 3. How can I track memory consumption by database object? a. SQL Server MVP and luminary, Kalen Delaney, once shared this query that shows consumption of the buffer pool in the local database: --- DEMO: Buffer counts by object & index.sql--- break down buffers by object (table, index) SELECT b.database_id, database_name = CASE b.database_id WHEN 32767 THEN 'Resource' ELSE db_name(b.database_id) END, p.object_id, Object_Name = object_name(p.object_id, database_id), p.index_id, buffer_count=count(*)FROM sys.allocation_units aJOIN sys.dm_os_buffer_descriptors b ON a.allocation_unit_id = b.allocation_unit_idJOIN sys.partitions p ON a.container_id = p.hobt_id WHERE object_id > 99 GROUP BY b.database_id, p.object_id, p.index_id ORDER BY buffer_count DESC 4. What other techniques are there for learning about memory consumption? a. You should learn about DBCC MEMORYSTATUS discussed at http://support.microsoft.com/?id=907877. (Thanks to Erland Sommarskog for pointing out this resource.) b. You should also read the excellent white paper entitled Troubleshooting Performance Problems in SQL Server 2005 Whitepaper located on TechNet at http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx. I hope this helps. Enjoy!-Ke