Reading Synapse query execution plans via dynamic management views

The primary diagnostic DMVs

• sys.dm_pdw_exec_requests: Identifies active, queued, and completed queries across the entire dedicated pool.
• sys.dm_pdw_request_steps: Breaks down an active query into its physical MPP execution steps, such as broadcasting data, shuffling distributions, or returning results to the client.
• sys.dm_pdw_sql_requests: Shows the exact T-SQL query fragments executing on individual distributions across compute nodes.

-- Step 1: Find the request ID of the slow query
SELECT request_id, [status], submit_time, total_elapsed_time, [label]
FROM sys.dm_pdw_exec_requests
WHERE [status] = 'Running'
ORDER BY total_elapsed_time DESC;

-- Step 2: Inspect which step is consuming time
SELECT step_index, operation_type, total_elapsed_time, [status]
FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID104857'
ORDER BY step_index;

Interpreting the output

• If operation_type shows RandomIDOperation or BroadcastMoveOperation taking 95% of total time, the query is stalled by network transfer due to uncolocated tables.
• If a step shows status = 'Running' for 20 minutes while its step type is ReturnOperation, check sys.dm_pdw_sql_requests to see if a single distribution has data skew and is processing all the rows alone.
• If a query shows status = 'Suspended', it is waiting in a Workload Management queue for concurrency slots or memory grants to become available.