Finding Slow Queries with Query History and system.query.history

1.Start with the Query History page

Before you can fix a slow query, you have to find it. In Databricks the usual starting point is Query History in the workspace sidebar. The documentation calls it a debugging tool: it lists previous query runs, and you can click any of them to see what happened. On a workspace enabled for serverless compute, the list covers more than SQL warehouses. It also includes SQL and Python queries run on serverless compute for notebooks and jobs.

Use the filters at the top of the page to narrow the list. You can filter by user, date range, compute, duration, query status, statement type, statement ID and query tags. For performance work, the duration filter is the useful one: sort or filter for the long runs on a given warehouse and you have your candidate list. The status filter finds queries that are Failed, Cancelled or still Queued.

Who can see what depends on permissions. You can always see your own query runs. To see other people's runs, you need at least CAN VIEW on the SQL warehouse that executed them. Queries shared with Run as Owner permissions appear in the history of the person who ran them, not the person who shared them. If a long-running query is holding up the warehouse right now, open it and click Cancel next to Status. That button only shows up while the query is running. Statements that use Lakeflow pipelines compute can only be canceled from the Pipelines UI.

Sources1

2.Reading the query details panel

Click a query's text and a summary panel opens on the right. It shows the status (Queued, Running, Finished, Failed or Cancelled), the user and compute details, the statement's UUID and the full statement text. Below the text are the metrics that tell you whether the run performed poorly.

Query metrics are the common analysis numbers. Some of them have a filter icon, which shows the percent of data pruned during scanning. Little pruning on a big table is an early sign of a full scan. Input/Output (IO) shows the data read and written. See longest operators for this query opens the Top operators panel, which lists the operators that ran longest. A small preview of the query profile DAG gives you a rough idea of how complex the query is.

The panel reports two different times, and they measure different things. Query wall-clock duration is the elapsed time from the start of scheduling to the end of execution. It is broken down into scheduling, query optimization and file pruning, and execution, so you can tell a query that waited from one that ran slowly. Aggregated task time is the total time across all cores of all nodes. It can be much longer than wall-clock time when tasks run in parallel, and shorter when tasks had to wait for available nodes.

Sources1

3.Finding slow queries at scale with system.query.history

The UI is fine for checking one warehouse by eye. To rank queries across the account, use the system table system.query.history. It holds records for SQL warehouses, serverless compute for notebooks and jobs, and Lakeflow pipelines, from every workspace in the account that is in the same region. Two limits apply. Only admins can read it by default; to give other users or groups access, Databricks recommends creating a dynamic view for each. Records also usually take up to an hour to appear, so it is not a live view.

Databricks provides example queries that point at the two most common warehouse-level problems. Queries with a high waiting_at_capacity_duration_ms were queued instead of running; the fix is to raise the warehouse's max_clusters so it can scale out. Queries with spilled_local_bytes > 0 needed more memory than was available; the fix is a larger warehouse size, or a query that does less work.

SELECT
  statement_id,
  executed_by,
  spilled_local_bytes / (1024 * 1024) AS spilled_mb,
  read_bytes / (1024 * 1024) AS read_mb,
  total_duration_ms,
  start_time,
  statement_text
FROM
  system.query.history
WHERE
  compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 7 DAY
  AND spilled_local_bytes > 0
ORDER BY
  spilled_local_bytes DESC
LIMIT 50

SELECT
  statement_id,
  executed_by,
  total_duration_ms,
  waiting_at_capacity_duration_ms,
  execution_duration_ms,
  start_time,
  statement_text
FROM
  system.query.history
WHERE
  compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 7 DAY
  AND  ?  > 0
ORDER BY
  waiting_at_capacity_duration_ms DESC
LIMIT 50

When comparing scan columns, keep in mind that read_bytes is not on the same scale as table_bytes (pruned_files_bytes + read_files_bytes). The file-size columns are compressed on-disk sizes. read_bytes counts the data actually read, including uncompressed data from the disk cache and any retried reads, so it can be larger than table_bytes. Once a record stands out, open its full profile in the UI:

Sources23

4.Checking the warehouse: is the query slow or just waiting?

Sometimes a query looks slow but its plan is fine; it simply sat in the queue. To check, click the SQL warehouse and open its Monitoring tab. The live statistics at the top show the warehouse status, running queries, queued queries and current cluster count. The Peak query count chart shows the highest number of running plus queued queries in each 5-minute window. The tab also has its own query history table. Hover over a duration there to see how much of it was scheduling and how much was running.

The sizing guidance gives you three signals. On the Monitoring page, Peak Queued Queries that stays above 0 means you may need a larger cluster size or more clusters. Query history shows which queries are the bottlenecks. In the query profile, Bytes spilled to disk means the warehouse may be too small. Every queue holds at most 1,000 queries, whatever the warehouse type.

Sources45

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.An aggregated task time far above the wall-clock duration means the query is inefficient or double-counting.Why is that wrong?

   Aggregated task time adds up the time across all cores of all nodes, so parallel execution makes it larger than wall-clock time as a matter of course.

   Covered in Reading the query details panel

2. 2.Queue time and disk spill have the same fix: a bigger warehouse.Why is that wrong?

   They have different fixes. Queue time (waiting_at_capacity_duration_ms) calls for a higher max_clusters so the warehouse can scale out. Disk spill calls for a larger warehouse size so queries get more memory.

   Covered in Finding slow queries at scale with system.query.history