What you will be able to do
- Open a query profile and know why one might be unavailable
- Use Top operators, the DAG metrics and verbose mode to find the slowest operator
- Read performance insights and match each one to its recommended fix
- Share a query profile by URL or as JSON
1.Opening a query profile
Once query history has pointed you to a slow run, the query profile shows why it was slow. It is a visual breakdown of how the query executed. You can see each operator with metrics such as time spent, rows processed and memory consumption. You can spot the slowest part of the query at a glance, and you can find common SQL mistakes such as exploding joins or full table scans.
To view a profile, you must either own the query or have at least CAN MONITOR on the SQL warehouse that ran it. The usual path is Query History → click the query → See query profile. There are other ways in. In the SQL editor, click the elapsed-time/rows link under the results. In a notebook attached to a SQL warehouse or serverless compute, click See performance under the cell. Lakeflow pipelines have a Query History tab. The jobs UI gives access for jobs run on SQL warehouses and serverless compute.
Checkpoint 1 of 6· Check yourself
An analyst did not run a query themselves and does not own it. What is the minimum access that lets them view its query profile?
Viewing a profile requires being the query owner or having at least CAN MONITOR on the SQL warehouse that executed the query.
“you must have at least CAN MONITOR permission on the SQL warehouse that executed the query.”Source: docs.databricks.com
This catches people out. A query served from the query cache did no execution work, so there is nothing to profile. To get around the cache, make a trivial change to the query, such as changing or removing the LIMIT, and run it again.
Sources1
2.Reading the profile: Top operators, the DAG and verbose mode
The detailed profile has summary metrics on the left and a graph on the right. The left side has three tabs. Details shows the query's summary metrics. Top operators lists the most expensive operators and is the quickest way to find where to optimize. Query text shows the full SQL. The right side is the directed acyclic graph (DAG) of operators. You can switch the metric it displays between Time spent, Memory peak and Rows, search for operators or columns, zoom, and click any operator to see its detailed metrics.
Some things can make the graph harder to read. Some non-Photon operations run as a group, and every operation in the group shows the same value as its parent operator for shared metrics. Metrics for some operations are hidden by default because they are unlikely to be the bottleneck. Turn on Enable verbose mode to see every operation and extra metrics. For Databricks SQL queries you can also choose Open in Spark UI from the kebab menu.
To find a bottleneck, you need to know what the common operators do. Scan reads from a data source. Join combines rows from several relations. Union concatenates rows from relations with the same schema. Hash / Sort groups rows by a key and applies aggregates such as SUM or COUNT. Filter keeps the rows that match a condition such as a WHERE clause. Shuffle redistributes data, which is expensive because it moves data between executors on the cluster.
Checkpoint 2 of 6· Match them up
Match each query profile operator to what it did.
Tap a term, then the definition that fits it.
These definitions come from the query profile documentation's list of common operations. Shuffle is singled out as expensive because it moves data across the cluster.
“Shuffle operations are expensive with regard to resources because they move data between executors on the cluster.”Source: docs.databricks.com
Checkpoint 3 of 6· Exam question
A `GROUP BY` query aggregating billions of rows completes successfully but takes far longer than expected. In the query profile, the aggregation operator shows a large "bytes spilled to disk" value and a peak memory figure well above the warehouse's available memory. What does this indicate, and what should the analyst try first?
Correct answer: A — The operator ran out of memory and spilled data to disk, so the analyst should increase the warehouse size or filter earlier to shrink the aggregated data volume.
- A. A large spill value paired with peak memory above the available limit is the classic signature of an out-of-memory aggregation writing intermediate state to disk. Sizing up the warehouse or shrinking the data being aggregated directly reduces the memory pressure causing the spill.
- B. Storage throttling shows up as I/O wait time on scan operators, not as a spill metric on an aggregation operator. This distractor misreads a memory-pressure symptom as a network problem, so it would not address the actual cause.
- C. Clustering improves file pruning during scans, but a spill on an in-memory aggregation operator is a memory sizing issue, not a data layout issue. Reclustering the table would not reduce the memory an already-scanned aggregation needs to hold.
- D. Queueing from concurrency contention delays when a query starts running, but the profile here shows an operator that is executing and spilling, meaning it already has compute. Adding clusters for concurrency would not shrink this operator's own memory footprint.
Sources1
3.Performance insights: Databricks names the problem for you
You don't have to read every DAG yourself. When a query runs, Databricks returns query performance insights. These flag opportunities to improve the query and report optimizations Databricks already applied. They appear in two places. The query details panel shows a summary ranked by estimated effect on total task duration, so the first item is the one most worth fixing. The Performance insights tab in the profile shows the full detail of each insight.
| Insight | Recommendation |
|---|---|
| Data spilled to disk because it did not fit in memory | Increase the warehouse size to add memory; reduce rows, columns or large-column size |
| The query waited in the warehouse queue | Increase the maximum number of clusters on the warehouse |
| The table scan reads many small files | Enable Predictive Optimization, run OPTIMIZE, or switch partitioned tables to liquid clustering |
| Delta data skipping statistics missing or incomplete | Collect Delta statistics to reduce bytes read |
| The query projects all columns from the table | Project only the columns you need |
| The join produces significantly more rows than it reads | Update the join condition or reduce input rows from both relations |
| Clustering or partitioning keys aren't used in scan filters | Add filters on those keys to reduce bytes read |
| Photon can't accelerate an operation | Review Photon limitations and use a supported execution path |
| Data is distributed unevenly across computing resources | Use key salting or pre-aggregation to balance the workload |
Some insights need no action. Insights labeled Accelerated describe optimizations Databricks has already applied. Examples are reading less data thanks to Automatic Liquid Clustering, choosing a broadcast join based on earlier runs, and running a short query through a fast path while the cluster was at capacity. For actionable insights, click Optimize to open Genie Code. If the fix is a change to the query, Genie Code rewrites the query and asks for your approval. If the fix is a table or compute change, it describes the recommended actions in plain language.
Checkpoint 4 of 6· Check yourself
The Performance insights tab shows an insight with an Accelerated label saying a broadcast join was chosen based on workload history. What should the analyst do?
Accelerated insights report what Databricks already did during execution. They are not problems to fix.
“They appear in the Performance insights tab with an Accelerated label and do not require action.”Source: docs.databricks.com
Checkpoint 5 of 6· Exam question
A join between a large fact table and a small dimension table finishes, but the query profile shows one task in the shuffle stage processing far more rows and taking dramatically longer than the other tasks in that same stage. What problem does this pattern point to, and what should the analyst investigate?
Correct answer: A — This pattern indicates data skew, where one key value has far more matches than others, so the analyst should check the join key's distribution and salt or filter the skewed value.
- A. One task handling far more rows and time than its peers in the same shuffle stage is the textbook signature of data skew: a small number of key values dominate the partition that task is assigned. Investigating the key distribution and salting or isolating the hot value is the standard remediation.
- B. An undersized warehouse would slow every task roughly proportionally to the data it holds, not create one dramatically slower task while its peers finish quickly. Resizing without addressing the skewed key would leave the imbalance between tasks unchanged.
- C. Missing statistics can affect the optimizer's choice between broadcast and shuffle joins, but it does not explain why one specific task within an already-chosen shuffle stage is overloaded. The uneven task, not the join strategy, is the symptom described here.
- D. Photon acceleration affects per-task execution speed but does not explain why the shuffle assigns disproportionate rows to a single task. Rewriting the join as unioned subqueries would not fix an underlying skewed key and adds unnecessary complexity.
Sources2
4.Sharing a profile for a second opinion
Diagnosis is often a team effort, and how you share a profile depends on the other person's access. If they have CAN MANAGE on the query, click Share to copy the profile URL. If they don't, or aren't in the workspace, click Download to save the profile as a JSON file. They can then open it from Query History → kebab menu → Import query profile (JSON). An imported profile is loaded only into the browser session and isn't saved in the workspace, so it has to be imported again each time.
Download the query profile as JSON from the query's details and send the file. The consultant imports it with Import query profile (JSON). It won't stay in their workspace, so they import it again each time they want to view it.
Checkpoint 6 of 6· Check yourself
A colleague imported a query profile JSON yesterday. Today it is gone from their workspace. Why?
An imported profile is never saved to the workspace, so it has to be imported again each time.
“When you import a query profile, it is dynamically loaded into your browser session and does not persist in your workspace.”Source: docs.databricks.com
Sources1
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Every run in query history has a query profile, so a missing profile means you lack permission.Why is that wrong?
A query answered from the query cache has no profile. Make a trivial change such as changing or removing the LIMIT so the query actually executes.
Covered in Opening a query profile
2.Every insight on the Performance insights tab is a problem the analyst needs to fix.Why is that wrong?
Insights with the Accelerated label describe optimizations Databricks already applied and need no action. Only the other insights call for a change.
Covered in Performance insights: Databricks names the problem for you
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“You can discover and fix common mistakes in SQL statements, such as exploding joins or full table scans.”
↩︎ Opening a query profile“you must have at least CAN MONITOR permission on the SQL warehouse that executed the query.”
↩︎ Opening a query profile“Click See performance to open the run history.”
↩︎ Opening a query profile“Top operators: Opens the Top operators panel which shows the most expensive operators used in your query.”
↩︎ Reading the profile: Top operators, the DAG and verbose mode“By default, metrics for some operations are hidden. These operations are unlikely to be the cause of performance bottlenecks.”
↩︎ Reading the profile: Top operators, the DAG and verbose mode“If the other user has the CAN MANAGE permission on the query, you can share the URL for the query profile with them.”
↩︎ Sharing a profile for a second opinion“To circumvent the query cache, make a trivial change to the query, such as changing or removing the LIMIT.”
↩︎ Exam trap 1“A query profile is not available for queries that run from the query cache.”
↩︎ Prediction“Shuffle operations are expensive with regard to resources because they move data between executors on the cluster.”
↩︎ Checkpoint“When you import a query profile, it is dynamically loaded into your browser session and does not persist in your workspace.”
↩︎ Checkpoint - 2.
“The query details panel shows a summary of insights, ranked by their estimated effect on total task duration.”
↩︎ Performance insights: Databricks names the problem for you“For insights that require a query change, Genie Code rewrites the query and presents the changes for your approval.”
↩︎ Performance insights: Databricks names the problem for you“Recommendation: Increase the maximum number of clusters on the warehouse to reduce queue time.”
↩︎ Performance insights: Databricks names the problem for you“These insights describe optimizations that Databricks already applied during query execution.”
↩︎ Exam trap 2“They appear in the Performance insights tab with an Accelerated label and do not require action.”
↩︎ Checkpoint