Linking memory allocations to cached plans

Question

Can I use the following query to find memory allocations for plans in the cache? How does this work? Are these memory allocations for only current connections?

select top 20 *

from sys.dm_os_memory_objects obj

      join sys.dm_os_memory_clerks cl on cl.page_allocator_address = obj.page_allocator_address

      join sys.dm_exec_cached_plans plans on plans.memory_object_address = obj.memory_object_address

      cross apply sys.dm_exec_sql_text(plans.plan_handle) as txt

order by cl.virtual_memory_reserved_kb desc

Answer 1

This article is old but it speaks about what you are looking for.

Do not forget to read comments.

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/04/ketan-duvedi.aspx

Cheers

---

MCP, MCTS, MCITP