Sql-server – solve high stolen memory

memorysql serversql-server-2012

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.

Best 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.

Related Question