Home / Databricks on Azure

Databricks on AzureCheat sheetIntermediate

Databricks query history cheat sheet: SQL for slow queries, queueing, scans and failures

Copy-paste SQL for system.query.history that shows where query time went, which warehouse is queueing, which queries scan too much, and what is failing.

Deepak Bhardwaj7 min read

Applies toAzure Databricks with Unity Catalog and the system.query schema enabled; SQL warehouses, serverless notebooks and jobs, Lakeflow pipelines

Someone says "the warehouse is slow". These queries show whether the time went on waiting, compiling, scanning, spilling or fetching results, and which user, job or dashboard caused it.

Records typically arrive within an hour. For a query running right now, use Query History in the workspace sidebar instead.

Note

Column names can change between releases. If a query fails on a column, check the current table reference or run DESCRIBE system.query.history.

Read the numbers first

Reading What it means Fix
High waiting_at_capacity_duration_ms Compute is up but full Raise max clusters, or move heavy work to its own warehouse
High waiting_for_compute_duration_ms Waiting for compute to be provisioned Check auto-stop and start-up behaviour; consider serverless
High compilation_duration_ms Slow metadata loading or optimization Simplify nested views; compact tables with many files
High execution_duration_ms, few files pruned Scanning too much Add selective filters; use liquid clustering on the filter columns
High spilled_local_bytes Ran out of memory and wrote to disk Reduce the data joined or aggregated; try a larger warehouse
High shuffle_read_bytes Large joins or aggregations Filter earlier; review the join order in the query profile
High result_fetch_duration_ms The client is pulling a big result Aggregate or limit the result; check the BI tool's settings

Slowest queries, with a time breakdown

SELECT statement_id,
       workspace_id,
       executed_by,
       compute.warehouse_id,
       client_application,
       round(total_duration_ms / 1000, 1)                AS total_s,
       round(waiting_for_compute_duration_ms / 1000, 1)  AS wait_compute_s,
       round(waiting_at_capacity_duration_ms / 1000, 1)  AS queued_s,
       round(compilation_duration_ms / 1000, 1)          AS compile_s,
       round(execution_duration_ms / 1000, 1)            AS execute_s,
       round(result_fetch_duration_ms / 1000, 1)         AS fetch_s
FROM system.query.history
WHERE start_time >= current_timestamp() - INTERVAL 24 HOURS
  AND execution_status = 'FINISHED'
ORDER BY total_duration_ms DESC
LIMIT 25;

To open the profile, log in to the workspace in workspace_id, because the table covers every workspace in your account in the same region. Then go to Query History, paste the statement_id into the Statement ID field, click the query and choose See query profile.

Queueing per warehouse, per hour

SELECT compute.warehouse_id,
       date_trunc('HOUR', start_time)                         AS hour,
       count(*)                                               AS statements,
       count_if(waiting_at_capacity_duration_ms > 0)          AS queued_statements,
       round(percentile_approx(waiting_at_capacity_duration_ms, 0.95) / 1000, 1) AS p95_queued_s
FROM system.query.history
WHERE start_time >= current_timestamp() - INTERVAL 7 DAYS
  AND compute.type = 'WAREHOUSE'
GROUP BY ALL
HAVING queued_statements > 0
ORDER BY p95_queued_s DESC
LIMIT 50;

If queueing appears at the same hour every day, look for a scheduled refresh or a batch job at that time before you add clusters.

Queries that scan too much

SELECT statement_id,
       executed_by,
       read_files,
       pruned_files,
       round(100 * try_divide(pruned_files, read_files + pruned_files), 1) AS pct_files_pruned,
       round(read_files_bytes / 1e9, 2)   AS gb_read,
       read_rows,
       produced_rows,
       round(total_duration_ms / 1000, 1) AS total_s
FROM system.query.history
WHERE start_time >= current_timestamp() - INTERVAL 24 HOURS
  AND statement_type = 'SELECT'
  AND from_result_cache = false
  AND read_files > 0
ORDER BY read_files_bytes DESC
LIMIT 25;

A low pct_files_pruned with many read_rows and few produced_rows means files were read and then mostly filtered out. That usually points to a filter that doesn't match the table's clustering, or a function wrapped around the filter column.

Tip

To compare with the table size, use pruned_files_bytes + read_files_bytes. Don't use read_bytes for this: it includes disk-cache data and retries, so it can be larger than the table.

Memory pressure: spill and shuffle

SELECT statement_id,
       executed_by,
       compute.warehouse_id,
       round(spilled_local_bytes / 1e9, 2) AS gb_spilled,
       round(shuffle_read_bytes / 1e9, 2)  AS gb_shuffled,
       round(total_task_duration_ms / execution_duration_ms, 1) AS avg_parallelism,
       round(execution_duration_ms / 1000, 1) AS execute_s
FROM system.query.history
WHERE start_time >= current_timestamp() - INTERVAL 24 HOURS
  AND spilled_local_bytes > 0
  AND execution_duration_ms > 0
ORDER BY spilled_local_bytes DESC
LIMIT 25;

Who is using the warehouse

SELECT coalesce(client_application, 'unknown')  AS client,
       executed_by,
       query_source.job_info.job_id             AS job_id,
       query_source.dashboard_id                AS dashboard_id,
       query_source.genie_space_id              AS genie_space_id,
       count(*)                                 AS statements,
       round(sum(total_task_duration_ms) / 3600000, 2) AS task_hours
FROM system.query.history
WHERE start_time >= current_timestamp() - INTERVAL 7 DAYS
  AND compute.warehouse_id = '<warehouse-id>'
GROUP BY ALL
ORDER BY task_hours DESC
LIMIT 25;

client_application comes from what the client reports, so treat it as a hint. If your teams set query tags, filter on them with query_tags['team'] = 'finance'. Tags are populated for SQL warehouses and serverless notebook and job compute.

Failures grouped by error

SELECT coalesce(nullif(regexp_extract(error_message, '\\[([A-Z0-9_.]+)\\]', 1), ''),
                left(error_message, 80)) AS error,
       count(*)                          AS failures,
       count(DISTINCT executed_by)       AS principals,
       min(start_time)                   AS first_seen,
       max(start_time)                   AS last_seen
FROM system.query.history
WHERE start_time >= current_timestamp() - INTERVAL 24 HOURS
  AND execution_status = 'FAILED'
GROUP BY ALL
ORDER BY failures DESC;

A new error with a recent first_seen and many principals usually means a shared change, such as a dropped table, a revoked grant or a network rule. execution_status has three values: FINISHED, FAILED and CANCELED.

Jobs slower than their weekly baseline

SELECT query_source.job_info.job_id AS job_id,
       round(percentile_approx(execution_duration_ms, 0.5)
             FILTER (WHERE start_time >= current_timestamp() - INTERVAL 1 DAY) / 1000, 1) AS p50_last_day_s,
       round(percentile_approx(execution_duration_ms, 0.5)
             FILTER (WHERE start_time < current_timestamp() - INTERVAL 1 DAY) / 1000, 1)  AS p50_prior_s,
       count(*) AS statements
FROM system.query.history
WHERE start_time >= current_timestamp() - INTERVAL 8 DAYS
  AND query_source.job_info.job_id IS NOT NULL
  AND execution_status = 'FINISHED'
GROUP BY ALL
HAVING p50_last_day_s > 2 * p50_prior_s
ORDER BY p50_last_day_s DESC;

The 2x threshold is only a starting point. A job's statements can vary a lot, so check the job's run history before raising a problem.

Cache hit rates per warehouse

SELECT compute.warehouse_id,
       count(*) AS statements,
       round(100 * count_if(from_result_cache) / count(*), 1) AS pct_result_cache,
       round(avg(read_io_cache_percent) FILTER (WHERE read_files > 0), 1) AS avg_disk_cache_pct
FROM system.query.history
WHERE start_time >= current_timestamp() - INTERVAL 7 DAYS
  AND compute.type = 'WAREHOUSE'
GROUP BY ALL
ORDER BY statements DESC;

What the table can and can't tell you

Item Detail
Default access Admins only
statement_text Returns <REDACTED> unless you are an account admin or in the databricks_pii_access account-level group
Coverage SQL warehouses, serverless notebooks and jobs, Lakeflow pipelines on serverless or classic compute. Don't assume classic notebooks and jobs are covered
compute.cluster_id Never populated, so you can't join to a cluster on it
Customer-managed keys statement_text and error_message need extra setup to read. Older records can't be decrypted
Retention Not stated on the table reference. Check how far back your data goes with the query below 
SELECT min(start_time) AS oldest_record FROM system.query.history;

Prevent it next time

Give team leads access through a view instead of the base table. Databricks recommends a dynamic view per user or group:

CREATE OR REPLACE VIEW ops.monitoring.query_history_scoped AS
SELECT statement_id, executed_by, compute, execution_status, start_time,
       total_duration_ms, waiting_at_capacity_duration_ms, execution_duration_ms,
       read_files, pruned_files, spilled_local_bytes, error_message
FROM system.query.history
WHERE is_account_group_member('platform-admins')
   OR executed_by = current_user();

GRANT SELECT ON TABLE ops.monitoring.query_history_scoped TO `data-engineers`;

Caution

Query text and error messages can contain customer values used as literals. Keep statement_text out of shared views unless the audience is allowed to see that data. If customer-managed keys are enabled on the system catalog, Databricks warns that existing grants on system.query and system.query.history are removed, so grant them again afterwards.

Then make the checks routine:

  • Save the queueing and failure queries as Databricks SQL alerts, so problems show up without anyone running them by hand.
  • Ask teams to set query tags on scheduled workloads, so you can trace a load to an owner quickly.
  • Review the top scans each month and fix the table layout for the worst offenders.

Related fixes