Systems Engineering and RDBMS

Finding top memory objects in SQL Server 2005

Posted by decipherinfosys on May 29, 2008

While troubleshooting a memory related performance issue at a client site, my colleague pointed out a query that can be used to find the top x memory consumers.  One can make use of sys.dm_os_memory_clerks and sys.dm_os_memory_objects DMVs to retrieve that information.  Here is the SQL:

select distinct pages_allocated_count, type
from sys.dm_os_memory_objects as domo
inner join (select top 20 page_allocator_address
from sys.dm_os_memory_clerks
order by multi_pages_kb desc) as IV
on domo.page_allocator_address = IV.page_allocator_address
order by pages_allocated_count desc

In the case of this particular client, we saw that the CLR’s memory clerk was the top culprit.  There was no need to make use of the CLR for what the client was trying to do.  We re-wrote that functionality as a simple stored procedure and removed the CLR usage to ease the memory pressure.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: