Snowflake, BigQuery & Databricks · Azure Synapse & Fabric

Recovering from severe tempdb exhaustion in Synapse Dedicated Pool

azureHardsynapsetempdb-exhaustiontroubleshootingmemory-tuning

Interview question

What happens when a rogue query exhausts tempdb in Synapse Dedicated Pool, and how do you remediate it?

How to answer

The short answer

In Azure Synapse Dedicated SQL Pool, tempdb is a shared physical resource distributed across all compute nodes. It is used to store temporary tables (#table), internal hash tables for join operations, sorting runs, and intermediate spool data.

When a poorly written query executes a massive Cartesian cross-join or an unpartitioned aggregation on billions of rows, the intermediate hash table can exceed available physical memory. Synapse begins spilling data to tempdb.

Symptoms of tempdb exhaustion

If an unconstrained query spills hundreds of gigabytes, it can fill 100% of available tempdb space on one or more compute nodes.

When this occurs:

  • The rogue query stalls or throws error code 1101 / 1105 (Could not allocate space for object in database 'tempdb').
  • All other concurrent queries running across the cluster freeze or fail, because they cannot allocate temporary workspace.
  • Ingestion pipelines time out.

Diagnostic and recovery runbook

To resolve the outage:

  • Identify the rogue query: Query sys.dm_pdw_exec_requests to find queries with massive elapsed times and high memory grants:
SQL
SELECT request_id, session_id, [status], total_elapsed_time, command
FROM sys.dm_pdw_exec_requests
WHERE [status] = 'Running'
ORDER BY total_elapsed_time DESC;

Next, terminate the blocking session:

  • Terminate the query: Cancel the offending request immediately:
SQL
KILL 'SID104857';

Take preventive measures to protect cluster resources:

  • Assign data science and ad-hoc analysts to a dedicated Workload Group with a strict CAP_PERCENTAGE_RESOURCE limit.
  • Enforce query timeout limits.
  • Educate developers to avoid SELECT DISTINCT * joins on large tables without explicit join .