7.3 Troubleshooting Slow & Failing Queries

Troubleshooting Slow & Failing Queries

Exam Focus: Databricks Data Analyst Associates must be adept at using the Query History interface and Query Profile visualizer to identify, diagnose, and resolve query performance bottlenecks and failures. Exam questions test your ability to interpret execution metrics (such as disk spill, task skew, and file scan volumes) and select the correct remediation strategy.

The Databricks SQL Diagnostic Toolkit

When a query runs slowly or fails, the first step is opening Query History in the Databricks SQL sidebar. Query History provides administrative and diagnostic metadata for every query executed on a SQL Warehouse within the past 30 days.

Key diagnostic details provided in Query History include:

• Statement Status: COMPLETED, FAILED, CANCELED, or QUEUED.
• Duration Breakdown: Execution Time, Compile Time, Queued Time, and Result Fetching Time.
• SQL Warehouse Info: Warehouse name, compute size (e.g., Small, Medium, Large), and cluster type (Serverless vs Classic).
• Query Profile Visualizer: A graphical DAG (Directed Acyclic Graph) showing exact tree operators (ScanExistingTable, Filter, HashAggregate, BroadcastHashJoin, SortMergeJoin) and per-operator metric statistics.

---

Troubleshooting Common Performance Bottlenecks

1. Data Spill to Disk (Memory Exhaustion)

Symptom: Query execution slows down significantly during large join or aggregation steps. The Query Profile displays high values for Spill (disk) and Spill (memory).

Root Cause: Disk spill occurs when an executor's allocated RAM (JVM heap / off-heap memory) is insufficient to hold intermediate data structures during sorting, hash aggregation, or shuffle join operations. Spark spills the excess data to local worker disk, incurring heavy read/write I/O penalties.

Query Profile Warning Indicator:
[HashAggregate] -> Spill (memory): 14.2 GiB | Spill (disk): 4.1 GiB

Remediation Strategies:

• Upsize the SQL Warehouse: Scaling up the SQL Warehouse size (e.g., from Medium to Large) increases RAM capacity per worker node.
• Filter Early: Move WHERE clause filters ahead of complex joins to reduce intermediate record volumes.
• Optimize Join Strategy: Enable broadcast joins for smaller lookup tables using hints:

  SELECT /*+ BROADCAST(dim_customer) */ f.order_id, d.customer_name
  FROM sales_fact f
  JOIN dim_customer d ON f.customer_id = d.customer_id;
  

---

2. Data Skew (Uneven Executor Workloads)

Symptom: A query gets stuck at 99% progress for a long time. In the Query Profile, inspecting task attempt durations shows that Max Task Duration is dramatically higher than Median Task Duration (e.g., 1 task takes 12 minutes while 100 tasks finish in 3 seconds).

Root Cause: Data skew occurs when data distribution across partition keys is uneven. For example, joining tables on a customer_id column where 50% of rows contain NULL or a default placeholder ('UNKNOWN') forces a single executor node to process half of the entire dataset.

Remediation Strategies:

• Filter Out Nulls/Placeholders: Exclude heavily skewed values before performing joins:

  SELECT *
  FROM orders o
  JOIN customers c ON o.customer_id = c.customer_id
  WHERE o.customer_id IS NOT NULL AND o.customer_id != 'UNKNOWN';
  

• Enable Adaptive Query Execution (AQE) Skew Join: Ensure AQE skew join handling is enabled:

  SET spark.sql.adaptive.skewJoin.enabled = true;
  

• Key Salting: For persistent high-cardinality skew, salt the join key by appending random integers to distribute skewed keys across multiple executor tasks.

---

3. Full Table Scans & Non-Sargable Predicates

Symptom: Query Profile reveals high Files Read and Bytes Read from Storage metrics that match total table size, indicating zero data skipping occurred despite having WHERE clauses.

Root Cause: Using non-sargable predicates (Search Argument Able) prevents Delta Lake from using file min/max statistics for data skipping. Wrapping filter columns in scalar functions forces Spark to evaluate every row across all files.

-- NON-SARGABLE (Bad): Forces full table scan because YEAR() wraps the column:
SELECT SUM(amount)
FROM sales_fact
WHERE YEAR(order_date) = 2026;

-- SARGABLE (Good): Enables Delta file min/max data skipping:
SELECT SUM(amount)
FROM sales_fact
WHERE order_date >= '2026-01-01' AND order_date <= '2026-12-31';

Remediation Strategies:

• Rewrite predicates to isolate table columns on one side of comparison operators.
• Run ANALYZE TABLE table_name COMPUTE STATISTICS; to update column statistics.
• Apply Liquid Clustering (CLUSTER BY) on heavily filtered query columns.

---

4. High Queued Time (Warehouse Concurrency Limits)

Symptom: Queries experience long execution delays, but the Query Profile shows very low active execution time while Queued Time accounts for 80%+ of overall duration.

Root Cause: The SQL Warehouse has reached its concurrent query execution capacity limit. Subsequent incoming queries are queued waiting for compute threads to free up.

Remediation Strategies:

• Enable Multi-Cluster Auto-scaling on the SQL Warehouse (e.g., setting Min Clusters = 1, Max Clusters = 5). When queueing occurs, the warehouse automatically spins up additional clusters to handle concurrent query demand.
• Route ad-hoc analyst queries and automated dashboard refreshes to separate dedicated SQL Warehouses.

---

Comprehensive Troubleshooting Reference Matrix