June 2, 2011 at 4:43 am
The estimated query plan does not contain a MemoryGrant attribute that would allow one to search the plan cache for top memory consuming queries.
I understand the reason for this in that the actual grant varies and depends upon the "required memory" and "additional memory" parameters as described here.
Are these parameters exposed anywhere? If not how can the highest memory consuming queries be identified? Capturing the actual execution plan for every query just to get the MemoryGrant would likely be prohibitively expensive.
Is the only other alternative to poll sys.dm_exec_query_memory_grants?
June 2, 2011 at 4:54 am
The below is the one am using at real time:
select text
query_plan
, requested_memory_kb
, granted_memory_kb
, used_memory_kb ,sql_handle
from sys.dm_exec_query_memory_grants MG
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(MG.plan_handle)
However I will look at the memeory utilization with the following which would give us where the memory been used at most:
select type,(sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB
from sys.dm_os_memory_cache_counters
Group by type
Order by plan_cache_in_GB desc
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply