Azure Synapse Analytics Errors, Connection, Throttling, and Query Performance Fixes

The first thing you need to establish is where in the query lifecycle the failure is occurring. Azure Synapse Analytics query performance problems break down into two broad phases: Compilation and Execution. You can't fix a compilation block with an execution-phase mitigation, so getting this right first saves you hours of chasing the wrong fix.

Run this against your dedicated SQL pool, substituting the actual session ID of the problematic query for @session_id. You can find active session IDs in the Azure Portal under Monitoring → SQL requests, or by querying sys.dm_pdw_exec_sessions.

-- Find your request_id first
SELECT session_id, request_id, status, submit_time, command
FROM sys.dm_pdw_exec_requests
WHERE status NOT IN ('Completed', 'Failed', 'Cancelled')
ORDER BY submit_time DESC;

Once you have the request_id (it looks like QID12345), plug it into the step-level query:

DECLARE @QID VARCHAR(16) = 'QID12345', @ShowActiveOnly BIT = 1;

SELECT step_index AS [StepIndex],
       'Execution' AS [Phase],
       operation_type + ' (' + location_type + ')' AS [Description],
       start_time AS [StartTime],
       end_time AS [EndTime],
       total_elapsed_time / 1000.0 AS [Duration],
       [status] AS [Status],
       CASE WHEN estimated_rows > -1 THEN estimated_rows END AS [EstimatedRowCount],
       CASE WHEN row_count > -1 THEN row_count END AS [ActualRowCount],
       command AS [TSQL]
FROM sys.dm_pdw_request_steps
WHERE request_id = @QID
  AND [status] = CASE @ShowActiveOnly WHEN 1 THEN 'Running' ELSE [status] END
ORDER BY step_index;

If rows come back, your query is in the Execution phase and you can see exactly which step is running. If no rows come back but the query shows as "Running" in Step 1, you're in a Compilation phase block, a fundamentally different problem. Note what the Description column says in either case; that value maps directly to the mitigation path.

If your query is stuck in the compilation phase, there are two distinct causes to rule out: compilation concurrency blocks and resource allocation blocks. They look similar from outside but need different fixes.

Run this to check whether a compilation block exists and who's causing it:

DECLARE @session_id INT = <your_session_id>, @QID VARCHAR(16) = '<request_id>';

SELECT waiting.session_id,
       waiting.request_id,
       'Compilation' AS [Phase],
       'Blocked by ' + blocking.session_id + ':' + blocking.request_id
         + ' when requesting ' + waiting.type
         + ' on ' + QUOTENAME(waiting.object_type) + waiting.object_name AS [Description],
       waiting.request_time AS [StartTime],
       DATEDIFF(ms, waiting.request_time, GETDATE()) / 1000.0 AS [Duration]
FROM sys.dm_pdw_waits AS waiting
INNER JOIN sys.dm_pdw_waits AS blocking
  ON waiting.object_type = blocking.object_type
  AND waiting.object_name = blocking.object_name
INNER JOIN sys.dm_pdw_exec_requests AS blocking_exec_request
  ON blocking.request_id = blocking_exec_request.request_id
WHERE waiting.session_id = @session_id
  AND waiting.state = 'Queued'
  AND blocking.state = 'Granted'
  AND waiting.type != 'Shared';

If the Description column says "Blocked by [session]:[request] when requesting...", you have a compilation concurrency block. This happens when a large volume of queries floods the system simultaneously. The fix is straightforward but sometimes uncomfortable: reduce concurrent query submissions. You may need to implement a queue at the application layer or adjust how batch jobs submit work.

If instead the block description mentions resource allocation, that means your query is waiting for a memory grant based on the resource class assigned to the executing user. Check which resource class that user is in and compare it against the workload's actual memory requirements. The fix is either bumping the user's resource class, waiting for blocking sessions to complete, or, when urgency demands it, killing the blocking session. Make that call carefully in production.

Once you've identified a specific step that's running slowly using the query from Step 1, you can go one layer deeper and see exactly what's happening at each distribution. This is where you find out if one distribution is running hot while others finished, the classic data skew signature, or whether a specific wait type is strangling throughput across all distributions.

DECLARE @QID VARCHAR(16) = '<request_id>', @StepIndex INT = <StepIndex>, @ShowActiveOnly BIT = 0;

WITH dists AS (
    SELECT request_id, step_index, 'sys.dm_pdw_sql_requests' AS source_dmv,
           distribution_id, pdw_node_id, spid, 'NativeSQL' AS [type],
           [status], start_time, end_time, total_elapsed_time, row_count
    FROM sys.dm_pdw_sql_requests
    WHERE request_id = @QID AND step_index = @StepIndex

    UNION ALL

    SELECT request_id, step_index, 'sys.dm_pdw_dms_workers' AS source_dmv,
           distribution_id, pdw_node_id, sql_spid AS spid, [type],
           [status], start_time, end_time, total_elapsed_time, rows_processed AS row_count
    FROM sys.dm_pdw_dms_workers
    WHERE request_id = @QID AND step_index = @StepIndex
)
SELECT sr.step_index, sr.distribution_id, sr.pdw_node_id, sr.spid,
       sr.type, sr.status, sr.start_time, sr.end_time,
       sr.total_elapsed_time, sr.row_count,
       owt.wait_type, owt.wait_time
FROM dists sr
LEFT JOIN sys.dm_pdw_nodes_exec_requests owt
  ON sr.pdw_node_id = owt.pdw_node_id
  AND sr.spid = owt.session_id
ORDER BY distribution_id;

Set @ShowActiveOnly to 0 to get the full picture across all distributions, not just currently active ones. Look at two things in the result set: the total_elapsed_time spread across distributions (huge variance = skew problem) and the wait_type column. The wait type tells you exactly what resource the slowest distribution is contending for, IO, memory, locks, which directly points to the mitigation. A skewed distribution usually means your hash distribution column is a poor choice for that table's query patterns.

Let's walk through the error codes you're most likely to encounter running Azure Synapse Analytics queries in production, and what to actually do about each one.

Error 8632, Expression services limit reached: This means your query is too complex for the optimizer to handle in a single pass. The error message says "simplify your query" which feels like unhelpful advice, but it's literally the fix. Break the query into multiple CTEs or temp table stages. Long chains of subqueries and deeply nested joins are the most common trigger. Rewriting to ANSI-92 style explicit JOINs (not WHERE-clause join conditions) also helps significantly, the optimizer handles them more efficiently.

Error 110806, Distributed query failed (tempdb full): When the error includes "Could not allocate space for object in database 'tempdb'", at least one compute node's tempdb partition has filled up. Immediate fix: pause and resume the pool. But then you need to understand what filled it. Large sorts, hash joins on non-distributed columns, and queries that generate massive intermediate result sets are the usual culprits. Increasing the user's resource class gives the operation more memory, which reduces how much spills to tempdb.

Error 110806, DMS operation failure (Error ID): This specific variant often means the Data Movement Service ran out of resources during inter-node data shuffling. Bump the user's resource class to match the workload size. Also check whether sys.dm_pdw_permanent_table_mappings can replace any references you have to sys.pdw_table_mappings, the permanent view is meaningfully faster for metadata retrieval.

Unhealthy CCIs, deleted/open row percentages: If your clustered columnstore indexes have a high percentage of deleted or open rows, the optimizer can't produce accurate estimates. Run delta store health checks and consider a scheduled ALTER INDEX REBUILD or REORGANIZE on the affected CCIs. This one sneaks up on teams because it degrades gradually rather than breaking suddenly.

-- Check CCI health across all tables
SELECT OBJECT_NAME(object_id) AS table_name,
       index_id,
       delta_store_hobt_id,
       state_desc,
       total_rows,
       deleted_rows,
       size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE state_desc IN ('OPEN', 'TOMBSTONE')
ORDER BY total_rows DESC;

One Azure Synapse query performance issue that catches people by surprise: when you run a query and it completes, but it was preceded by several CREATE STATISTICS statements that ran automatically. These auto-created statistics are usually a good thing, they help the optimizer. But there are two failure modes worth knowing.

Statistics created unexpectedly, causing query delay: If you see CREATE STATISTICS statements firing immediately after slow query submissions, you're experiencing a delay from auto-create statistics. The statistics job kicks in because the optimizer detected it needed fresh stats to plan the query, and that extra work added latency. This is mostly benign but worth monitoring. Pre-creating statistics on your most-queried columns eliminates this overhead.

Statistics creation failing after 5 minutes: This is a harder problem. The auto-statistics job times out before it can finish, leaving the optimizer without good estimates, which cascades into bad query plans. The fix is to create those statistics manually and explicitly:

-- Manually create statistics on high-impact columns
CREATE STATISTICS stats_customer_id ON dbo.orders(customer_id);
CREATE STATISTICS stats_order_date ON dbo.orders(order_date);
CREATE STATISTICS stats_product_id ON dbo.order_items(product_id);

Once you've manually created stats on your key join and filter columns, the auto-stats process has less work to do and timeout failures drop dramatically. Also review the distribution policy on your tables, if a table is distributed on a column that doesn't match your most common join predicate, you're generating unnecessary data movement at every query and the query optimizer is working with poor cardinality estimates from the start.

To audit your table distribution policies alongside index types, run this diagnostic:

SELECT t.object_id,
       SCHEMA_NAME(t.schema_id) AS [schema_name],
       t.name AS table_name,
       tdp.distribution_policy_desc,
       c.name AS hash_distribution_column_name
FROM sys.tables t
JOIN sys.pdw_table_distribution_properties tdp
  ON t.object_id = tdp.object_id
LEFT JOIN sys.pdw_column_distribution_properties cdp
  ON t.object_id = cdp.object_id AND cdp.distribution_ordinal = 1
LEFT JOIN sys.columns c
  ON cdp.object_id = c.object_id AND cdp.column_id = c.column_id
ORDER BY t.name;

If you find large fact tables distributed on columns that aren't your primary join keys, that's a redesign conversation, but it's the kind of structural fix that makes every subsequent query faster, not just the one you're debugging today.