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
Last activity: Jul 5, 2025, 06:00 PM