Home / Data Platform / Warehouses

Data PlatformTroubleshootingAdvanced

Sev1: Synapse dedicated SQL pool — every query queued, dashboards timing out

Clear a Synapse dedicated SQL pool where queries pile up in the queue. Find what holds concurrency slots, kill or isolate runaway loads, use workload groups and classifiers to protect BI, and scale if you must.

Deepak Bhardwaj2 min read

Applies toAzure Synapse Analytics dedicated SQL pools (formerly SQL DW), DMVs sys.dm_pdw_*, workload management

Symptoms

  • Power BI and reports time out; simple queries take minutes before they start.
  • sys.dm_pdw_exec_requests shows many requests in Suspended state waiting for resources.

1. Who's running and who's waiting?

SELECT r.request_id, r.status, r.submit_time, r.start_time, r.total_elapsed_time/1000 AS elapsed_s,
       r.resource_class, r.group_name, r.importance, s.login_name, LEFT(r.command, 100) AS cmd
FROM sys.dm_pdw_exec_requests r
JOIN sys.dm_pdw_exec_sessions s ON r.session_id = s.session_id
WHERE r.status IN ('Running', 'Suspended')
ORDER BY r.status, r.submit_time;

-- What are queued requests waiting on?
SELECT w.request_id, w.type, w.state, w.object_name, w.priority
FROM sys.dm_pdw_waits w
JOIN sys.dm_pdw_exec_requests r ON w.request_id = r.request_id
WHERE r.status = 'Suspended';

Typical picture: a few loads running in a large resource class (or a workload group with high REQUEST_MIN_RESOURCE_GRANT_PERCENT) consume most of the pool's memory, so everything else waits.

2. Immediate relief

Kill the runaway request (after checking with its owner if possible):

KILL 'QID123456';

Pause less critical loads (ADF/Synapse pipelines) until BI traffic has recovered.

3. Isolate workloads properly

Create workload groups so loads can't starve reporting:

CREATE WORKLOAD GROUP wg_loads WITH (
  MIN_PERCENTAGE_RESOURCE = 30, CAP_PERCENTAGE_RESOURCE = 60,
  REQUEST_MIN_RESOURCE_GRANT_PERCENT = 10);

CREATE WORKLOAD GROUP wg_bi WITH (
  MIN_PERCENTAGE_RESOURCE = 40, CAP_PERCENTAGE_RESOURCE = 100,
  REQUEST_MIN_RESOURCE_GRANT_PERCENT = 2.5);

CREATE WORKLOAD CLASSIFIER wc_loads WITH (WORKLOAD_GROUP = 'wg_loads', MEMBERNAME = 'adf_loader', IMPORTANCE = NORMAL);
CREATE WORKLOAD CLASSIFIER wc_bi WITH (WORKLOAD_GROUP = 'wg_bi', MEMBERNAME = 'powerbi_svc', IMPORTANCE = HIGH);

Small grant percentages give more concurrency for short BI queries; loads get bigger grants but are capped. Check the minimum grant values allowed at your service level (DWU).

4. Scale if needed

az synapse sql pool update -g rg-syn --workspace-name syn-prod -n dwprod --performance-level DW1500c

Warning

Scaling cancels running queries and takes a few minutes. Use it when the queue is already blocking everything, and announce it.

5. Fix the long-running queries

Common causes in dedicated pools:

  • Data movement (ShuffleMoveOperation, BroadcastMoveOperation) from distribution mismatches — check with sys.dm_pdw_request_steps.
  • Missing or stale statistics: UPDATE STATISTICS dbo.FactSales;
  • Poor columnstore quality after many small loads: rebuild indexes.
SELECT step_index, operation_type, location_type, status, total_elapsed_time, row_count
FROM sys.dm_pdw_request_steps WHERE request_id = 'QID123456' ORDER BY step_index;

Prevent it next time

  • Workload groups and classifiers for every service account; no users in large static resource classes by default.
  • Alerts on queued query count and wait times.
  • Schedule heavy loads outside BI peak hours; consider result set caching for repeated dashboard queries.

Related fixes