Sample Header Ad - 728x90

Azure Synapse dedicated pool high compute node memory

-2 votes
1 answer
51 views
Queries are running slow on azure synapse dedicated pool and all the compute and control nodes' memory usage is touching 100%. Some days back, we restarted the pool (pause and resume) and noticed the control node memory dropped to 3% (compute node remained at 100%) and it appears that queries started working fine for a week, and now the memory is back to 100% Now there are a couple of questions I have: 1. Is this normal that memory usage for control and compute nodes is always at 100%? 2. My understanding is that the compute node's job is to distribute queries to the distribution node, so why is its memory always 100%? I am using the following query to get the usage
SELECT  
  (pc1.cntr_value/1048576.0) as Curr_Mem_GB,  
  (pc2.cntr_value/1048576.0) as Max_Mem_GB,
  pc1.cntr_value * 100.0/pc2.cntr_value AS Memory_Utilization_Percentage,
  pc1.pdw_node_id
FROM
-- pc1: current memory
sys.dm_pdw_nodes_os_performance_counters AS pc1
-- pc2: total memory allowed for this SQL instance
JOIN sys.dm_pdw_nodes_os_performance_counters AS pc2
  ON pc1.object_name = pc2.object_name
  AND pc1.pdw_node_id = pc2.pdw_node_id
WHERE pc1.counter_name = 'Total Server Memory (KB)'
AND pc2.counter_name = 'Target Server Memory (KB)'
Asked by hassan ammar
Jul 4, 2025, 10:56 AM
Last activity: Jul 5, 2025, 06:00 PM