THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

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 database

2.  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 a

JOIN sys.dm_os_buffer_descriptors b

   ON a.allocation_unit_id = b.allocation_unit_id

JOIN 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!

 

-Kev

Published Tuesday, October 14, 2008 12:07 PM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jason said:

Also, I have found sys.dm_exec_query_memory_grants nice in reactively troubleshooting. For example, if a plan changes and starts to hash join, you can see 30 instances of the sproc waiting for a 200MB memory grant. With the exec DMF's, you can get the text and plan too.

October 14, 2008 10:24 PM
 

KKline said:

Great input, Jason!  Keep it coming!

October 16, 2008 12:43 PM
 

Borje Osterdahl said:

In FAQ 1 you mention 'CPU consumption by database' and that is what I am interested in. But I cannot find out in the FAQ where I can find it. Can you please make it more clear.

October 23, 2008 5:36 AM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement