Right-sizing Azure Databricks clusters with system.compute.node_timeline: SQL for CPU, memory, swap, disk and idle workers

#What the tables give you

system.compute.node_timeline holds one row per node per minute. system.compute.clusters holds the configuration history, with a new row on each change. system.compute.node_types maps a VM type to its cores and memory. Together they answer "is this cluster the right size?" with evidence instead of guesswork.

Key node_timeline columns: cluster_id, instance_id, driver (boolean), start_time, end_time, cpu_user_percent, cpu_system_percent, cpu_wait_percent, mem_used_percent, mem_swap_percent, network_sent_bytes, network_received_bytes, disk_free_bytes_per_mount_point (map), node_type.

Note

Caveats before you trust any number:

• No serverless compute or SQL warehouses. Classic compute only.
• Nodes that ran for under about 10 minutes might not appear.
• Records typically arrive within an hour.
• mem_used_percent includes background processes and the OS page cache, so high memory isn't automatically pressure. Swap is the stronger signal.
• Cluster records are regional. Run these queries from a workspace in the same region as the compute.

#Latest cluster config (reuse everywhere)

CREATE OR REPLACE TEMP VIEW cluster_latest AS
SELECT *
FROM   system.compute.clusters
QUALIFY row_number() OVER (PARTITION BY workspace_id, cluster_id ORDER BY change_time DESC) = 1;

#Utilization per cluster, last 14 days

Average and p95 CPU, memory and swap, split driver and worker:

SELECT n.cluster_id, c.cluster_name, c.cluster_source, n.driver, n.node_type,
       count(DISTINCT n.instance_id)                                     AS nodes_seen,
       round(avg(n.cpu_user_percent + n.cpu_system_percent), 1)          AS avg_cpu,
       round(percentile_approx(n.cpu_user_percent + n.cpu_system_percent, 0.95), 1) AS p95_cpu,
       round(avg(n.cpu_wait_percent), 1)                                 AS avg_iowait,
       round(avg(n.mem_used_percent), 1)                                 AS avg_mem,
       round(percentile_approx(n.mem_used_percent, 0.95), 1)             AS p95_mem,
       round(max(n.mem_swap_percent), 1)                                 AS max_swap,
       count(*)                                                          AS node_minutes
FROM   system.compute.node_timeline n
LEFT JOIN cluster_latest c USING (workspace_id, cluster_id)
WHERE  n.start_time >= current_timestamp() - INTERVAL 14 DAYS
GROUP  BY ALL
ORDER  BY node_minutes DESC;

#Reading the result

#Memory per node in GB, not percent

SELECT n.cluster_id, n.node_type, t.memory_mb / 1024 AS node_mem_gb, t.core_count,
       round(percentile_approx(n.mem_used_percent, 0.95) * t.memory_mb / 1024 / 100, 1) AS p95_used_gb
FROM   system.compute.node_timeline n
JOIN   system.compute.node_types t USING (account_id, node_type)
WHERE  n.start_time >= current_timestamp() - INTERVAL 7 DAYS AND NOT n.driver
GROUP  BY n.cluster_id, n.node_type, t.memory_mb, t.core_count
ORDER  BY p95_used_gb DESC;

#Swap events (the real memory alarm)

SELECT cluster_id, instance_id, driver,
       min(start_time) AS first_swap, max(end_time) AS last_swap,
       count(*) AS minutes_swapping, max(mem_swap_percent) AS peak_swap
FROM   system.compute.node_timeline
WHERE  start_time >= current_timestamp() - INTERVAL 3 DAYS AND mem_swap_percent > 0
GROUP  BY ALL
ORDER  BY minutes_swapping DESC;

Sustained swap on executors usually comes just before ExecutorLostFailure or GC storms. Match the times against failed job runs.

#Idle workers on autoscaling clusters

Minutes in which a worker was below 5% CPU while the cluster was running:

SELECT n.cluster_id, c.cluster_name, c.min_autoscale_workers, c.max_autoscale_workers,
       c.auto_termination_minutes,
       count_if(n.cpu_user_percent + n.cpu_system_percent < 5)                    AS idle_worker_minutes,
       count(*)                                                                    AS worker_minutes,
       round(100 * count_if(n.cpu_user_percent + n.cpu_system_percent < 5) / count(*), 1) AS idle_pct
FROM   system.compute.node_timeline n
JOIN   cluster_latest c USING (workspace_id, cluster_id)
WHERE  n.start_time >= current_timestamp() - INTERVAL 14 DAYS AND NOT n.driver
GROUP  BY ALL
HAVING worker_minutes > 600
ORDER  BY idle_worker_minutes DESC;

A high idle_pct with min_autoscale_workers well above 1 means the floor is too high. On all-purpose clusters, check auto_termination_minutes too.

#Concurrent node count over time (peak sizing)

SELECT cluster_id, date_trunc('MINUTE', start_time) AS minute,
       count_if(NOT driver) AS workers
FROM   system.compute.node_timeline
WHERE  cluster_id = '<cluster-id>' AND start_time >= current_timestamp() - INTERVAL 2 DAYS
GROUP  BY ALL
ORDER  BY minute;

If the cluster sits at max_autoscale_workers for long periods while CPU is saturated, the ceiling is capping throughput. If it never gets close to the max, lowering the max reduces the risk of a cost spike for free.

#Local disk filling up

disk_free_bytes_per_mount_point is a map from mount point to free bytes. Key names vary by image, so list them first:

SELECT DISTINCT explode(map_keys(disk_free_bytes_per_mount_point)) AS mount
FROM   system.compute.node_timeline
WHERE  start_time >= current_timestamp() - INTERVAL 1 DAY;

Then find the lowest free space per node:

SELECT cluster_id, instance_id, driver, m.key AS mount,
       round(min(m.value) / power(1024, 3), 1) AS min_free_gb
FROM   system.compute.node_timeline
LATERAL VIEW explode(disk_free_bytes_per_mount_point) m AS key, value
WHERE  start_time >= current_timestamp() - INTERVAL 3 DAYS
GROUP  BY ALL
HAVING min_free_gb < 10
ORDER  BY min_free_gb;

#Tie utilization to cost

Join DBUs to the same clusters to rank savings potential:

WITH util AS (
  SELECT cluster_id, avg(cpu_user_percent + cpu_system_percent) AS avg_cpu
  FROM   system.compute.node_timeline
  WHERE  start_time >= current_timestamp() - INTERVAL 30 DAYS AND NOT driver
  GROUP  BY cluster_id
), dbus AS (
  SELECT usage_metadata.cluster_id AS cluster_id, sum(usage_quantity) AS dbus
  FROM   system.billing.usage
  WHERE  usage_date >= current_date() - INTERVAL 30 DAYS AND usage_metadata.cluster_id IS NOT NULL
  GROUP  BY 1
)
SELECT d.cluster_id, c.cluster_name, c.worker_node_type, round(d.dbus) AS dbus_30d,
       round(u.avg_cpu, 1) AS avg_worker_cpu
FROM   dbus d JOIN util u USING (cluster_id)
LEFT JOIN cluster_latest c USING (cluster_id)
ORDER  BY d.dbus * (100 - u.avg_cpu) DESC
LIMIT  25;

The ordering puts expensive clusters with low utilization at the top. That's your right-sizing backlog.

Tip

Change one variable at a time (worker count, VM size or family) and compare the job's duration and DBUs over a week of runs, not a single run. Halving workers often lengthens runtime by less than half when CPU was low, which is where the savings come from.

#Prevent it next time

• Schedule the utilization and swap queries as Databricks SQL alerts per team.
• Encode the sizes you've proven in compute policies, as allowed node types and worker ranges.
• Re-check after every runtime upgrade or major data volume change. The right size drifts.