solve high stolen memory

Posted on

Question :

I’ve a SQL Server 2012 SP2 with max memory set to 13GB but there is 8GB of stolen memory (SQLServer:Memory Manager – Stolen Server Memory (KB)). I know stolen memory is memory taken from the bufferpool to do sorting and hashing operations.

But how can I identify what process/request is using all that stolen memory, or what query is doing a lot of hashing/sorting?

Update
Further research showed that MEMORYCLERK_XE was using 4.3GB. This could be fixed by SP3. We will schedule this update and monitor if the problem is fixed.

Answer :

You could try querying various dmvs with the following script.

SELECT TEXT,
   request_time,
   grant_time,
   query_cost,
   timeout_sec,
   wait_order,
   wait_time_ms,
   group_id,
   requested_memory_kb,
   granted_memory_kb,
   required_memory_kb,
   used_memory_kb,
   max_used_memory_kb,
   ideal_memory_kb,
   query_plan,
   'all information >',
   *
FROM   sys.dm_exec_query_memory_grants qmg
   OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(plan_handle) qp

It queries the following dmvs for information:

This script was handed over to me by Ramesh Meyyappan at a course on “SQL Server Performance Tuning and Optimisation” at Microsoft SA, Wallisellen, Switzerland.

Leave a Reply

Your email address will not be published. Required fields are marked *