6.2 Query History & Execution Profile Inspection

Query History & Execution Profile Inspection

In Databricks SQL, optimizing query performance requires moving beyond execution runtime numbers to inspect granular hardware and operator telemetry. The Query History interface and graphical Execution Profile provide data analysts with deep visibility into physical execution DAGs (Directed Acyclic Graphs), task distributions, resource bottlenecks, and Photon Engine utilization.

---

Query History Interface & Search Capabilities

The Query History page logs every query executed within a Databricks SQL Warehouse. Databricks retains query metrics for 30 days, offering an extensive audit and diagnostic window for historical analysis.

+-----------------------------------------------------------------------------------------+
|                               Databricks SQL Query History                              |
+-----------------------------------------------------------------------------------------+
| Filters: [ User: All ] [ Warehouse: BI_Analytics ] [ Status: Success ] [ Time: 7 Days ] |
+-----------------------------------------------------------------------------------------+
| Query ID | Statement                 | User        | Duration | Wall Time | Status |
| 8f4a12c9 | SELECT customer_id...     | j.doe@co    | 4m 12s   | 4m 10s    | SUCCESS|
| a3b719e0 | MERGE INTO sales_fact...  | ETL_Service | 12m 45s  | 12m 40s   | SUCCESS|
| f9e821a4 | SELECT * FROM raw_logs... | a.smith@co  | 15s      | 14s       | FAILED |
+-----------------------------------------------------------------------------------------+

Analysts can filter Query History using several key dimensions:

• Statement / Query ID: Search by specific SQL text or unique execution UUID.
• User / Service Principal: Audit queries triggered by individual analysts or automated BI service accounts.
• SQL Warehouse: Compare performance across Serverless, Pro, or Classic warehouse instances.
• Duration / Status: Isolate long-running queries, canceled statements, or runtime execution errors.

---

Graphical Execution Profile Structure

Selecting an individual query entry opens the Execution Profile. The Execution Profile renders the query's physical execution plan as a top-down or left-to-right DAG composed of interconnected operator nodes.

Each node represents a distinct physical processing stage (e.g., PhotonScan, PhotonHashAggregate, Filter, Exchange). Clicking on an operator node expands a details panel displaying execution metrics, memory consumption, and task timing distributions.

Key Metrics to Inspect in Execution Profiles

---

Diagnosing Common Query Bottlenecks

1. Memory Spilling (Bytes Spilled to Disk)

Spill to Disk occurs when an execution task (such as a large sort, window function, or hash join build table) exceeds its allotted executor memory segment. When RAM is exhausted, Spark spills intermediate partitions to local executor disk drives, causing severe disk IO bottlenecks.

• Diagnostic Indicator: In the Execution Profile node, Spill (Memory) and Spill (Disk) metrics display non-zero values (e.g., Spill (Memory): 45.2 GB, Spill (Disk): 12.8 GB).
• Remediation Strategies:
  1. Increase the compute size of the SQL Warehouse (e.g., scaling from Medium to Large), which increases memory per core.
  2. Optimize query logic by filtering early or selecting only necessary columns to reduce intermediate payload sizes.
  3. Replace Sort-Merge joins with Broadcast Hash joins by ensuring dimension tables are appropriately sized.

2. Data Skew

Data Skew occurs when data partitions are unevenly distributed across worker tasks. A single task may process 90% of a skewed join key (e.g., NULL values or high-frequency default customer IDs), forcing 127 cluster cores to sit idle while 1 core struggles to complete.

Task Execution Time Distribution (Data Skew Example):
Min Task Time:    1.2s  |==================|
Median Task Time: 1.5s  |====================|
Max Task Time:  180.4s  |=========================================================================>| (SKEW)

• Diagnostic Indicator: Extreme variance between task duration metrics within a node. The Max Task Duration (or 99th percentile) is order-of-magnitude larger than the Median Task Duration.
• Remediation Strategies:
  1. Filter out NULL or placeholder keys prior to performing join operations.
  2. Enable Databricks Adaptive Query Execution (AQE) skew join handling:

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

  3. Apply salting techniques to uniformize partition distribution on heavily skewed join keys.

3. Missing File Pruning (Unoptimized Data Scans)

When queries execute against unpartitioned or unindexed Delta tables, file scanners must evaluate every single Data File in cloud storage.

• Diagnostic Indicator: High Files Scanned count (e.g., 50,000 files) matching total table file count, with zero or low Files Pruned.
• Remediation Strategies:
  1. Execute OPTIMIZE table_name ZORDER BY (frequent_filter_column) to group co-located values within parquet files.
  2. Apply Liquid Clustering (CLUSTER BY (column_a, column_b)) on high-cardinality query filter columns.

---

Real-World Scenario: Investigating Dashboard Delay & Disk Spill

A senior data analyst notices that a daily revenue summary query on a 1-billion-row transactions dataset degraded from 30 seconds to 14 minutes.

1. Query History Investigation: The analyst opens Query History, filters by the query string, and observes wall-clock execution time spiked to 840 seconds.
2. Execution Profile Inspection: Opening the graphical profile reveals that 82% of the query time was concentrated in a PhotonHashJoin node. Expanding node metrics reveals:
   • Spill (Memory): 112 GB
   • Spill (Disk): 34 GB
   • Task Execution Time Max: 410s, Median: 4s (Indicating severe Skew + Memory Spill).
3. Root Cause Analysis: A recent upstream ETL bug introduced millions of default dummy records with customer_id = -1. The join operation routed all -1 records to a single hash join bucket, exceeding task memory and forcing 34 GB of disk spill.
4. Resolution: The analyst updated the query predicate to exclude invalid customer IDs prior to joining (WHERE customer_id != -1). Re-running the query eliminated disk spill (Spill to Disk: 0 Bytes) and restored execution time to 22 seconds.