1. Find the slow request and its steps
SELECT TOP 10 request_id, status, total_elapsed_time/1000 AS elapsed_s, resource_class, command
FROM sys.dm_pdw_exec_requests
WHERE status NOT IN ('Completed','Failed','Cancelled') OR submit_time > DATEADD(hour, -2, GETDATE())
ORDER BY total_elapsed_time DESC;
SELECT step_index, operation_type, location_type, status, total_elapsed_time/1000 AS elapsed_s, row_count
FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID12345'
ORDER BY step_index;
Steps like ShuffleMoveOperation or BroadcastMoveOperation with large row counts mean data is being moved between distributions. That's usually the expensive part.
2. Check table skew
DBCC PDW_SHOWSPACEUSED('dbo.FactSales');
Compare rows across the 60 distributions. If the largest distribution holds much more than the average (more than about 10% skew), queries wait on that one distribution.
3. Choose the right distribution
| Table type | Distribution |
|---|---|
| Large fact table (> ~60 M rows) | HASH on a column with many distinct values, few NULLs, and used in joins (for example CustomerKey) |
| Small dimension (< ~2 GB) | REPLICATE |
| Staging / no clear key | ROUND_ROBIN |
Bad hash keys: dates (all of today's data goes to one distribution), low-cardinality columns, and columns with many NULLs or default values.
Redistribute with CTAS:
CREATE TABLE dbo.FactSales_new
WITH (DISTRIBUTION = HASH(CustomerKey), CLUSTERED COLUMNSTORE INDEX)
AS SELECT * FROM dbo.FactSales;
RENAME OBJECT dbo.FactSales TO FactSales_old;
RENAME OBJECT dbo.FactSales_new TO FactSales;
Joining two fact tables on the same hash column avoids a shuffle (a distribution-compatible join).
4. Statistics
The optimizer relies on statistics to choose the data movement strategy. Stale statistics give bad plans:
UPDATE STATISTICS dbo.FactSales;
-- create on join/filter columns if auto-create is off
CREATE STATISTICS st_FactSales_CustomerKey ON dbo.FactSales (CustomerKey);
Update statistics after every large load.
5. Columnstore health
Small, open (delta) row groups make scans slow:
SELECT t.name AS table_name, rg.state_desc, COUNT(*) AS row_groups, AVG(rg.total_rows) AS avg_rows
FROM sys.dm_pdw_nodes_db_column_store_row_group_physical_stats rg
JOIN sys.pdw_nodes_tables nt ON rg.object_id = nt.object_id AND rg.pdw_node_id = nt.pdw_node_id AND rg.distribution_id = nt.distribution_id
JOIN sys.pdw_table_mappings tm ON nt.name = tm.physical_name
JOIN sys.tables t ON tm.object_id = t.object_id
GROUP BY t.name, rg.state_desc
ORDER BY t.name;
Average row groups far below 1,048,576 rows → loads use too little memory (a small resource class) or tiny batches. Rebuild with ALTER INDEX ALL ON dbo.FactSales REBUILD; using a larger resource class.
6. Concurrency and resource classes
Queries in larger resource classes get more memory but use more concurrency slots. If queries queue (status = 'Suspended'), check workload management (workload groups and classifiers) before scaling DWUs.
Tip
Many slow Synapse queries come down to "the fact table was hash-distributed on a date column". Check distribution choices first; that one change often turns hours into minutes.