0

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.

4
  • You should first apply SQL Server 2012 SP3 although this might not be related to your issue but many memory fixes are done from SP1 to SP3. Other question is are you really facing performance issue ? Stolen memory is not just only for sorting or hash operations it is for other uses as well. This memory is used for purpose other than database pages – Shanky May 18 '16 at 11:20
  • Is there a way to see what's allocating the 8GB, maybe there's something I can finetune? 60% of my memory is 'stolen', is this a normal behavior? – Frederik Vanderhaegen May 18 '16 at 12:26
  • Not that I know, because it not only include memory for Sort and hash but also for store internal data structures such as locks, transaction context, and connection information. – Shanky May 18 '16 at 12:32
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.