Finding top memory objects in SQL Server 2005

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.