sys.dm_os_performance_counters - Lock Memory (KB)

• dunno_developer

  SSC Enthusiast

  Points: 164

  More actions

  February 25, 2016 at 8:41 am

  #324543

  Hi

  We have a fairly highly intensive batch processing db.

  SQL 2008 R2 SP3

  48CPU

  512GB RAM

  +-5TB DB Size

  Most nights, the Lock Memory counter stays around 20/25GB throughout the batch.

  However, we have started to see the lock memory counter increase well over 150GB and as such noticed some performance degradation.

  SELECT 'Lock Memory (KB)', 'Total amount of dynamic memory the server is using for locks.', cntr_value AS Mem_KB , cntr_value / 1024.0 AS Mem_MB ,CONVERT(NUMERIC(28,2),(cntr_value/1024.0)/1024.0) AS Mem_GB

  FROM sys.dm_os_performance_counters

  WHERE counter_name = 'Lock Memory (KB)'

  Has anyone ever encountered this before?

  Does anyone know how to see what spid/query has the memory lock allocated?

  Thanking you in advance

  D

• r.dragoi-1095738

  SSCrazy

  Points: 2022

  More actions

  March 1, 2016 at 1:49 am

  #1862025

  Hi,

  See if this one helps :

  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,

  *

  from sys.dm_exec_query_memory_grants qmg

  outer apply sys.dm_exec_sql_text(sql_handle) a

  outer apply sys.dm_exec_query_plan(plan_handle) b

  Cheers,

  Robert