What you will be able to do
- Open a query's Query Profile and use Most Expensive Nodes and operator statistics to find the bottleneck
- Recognise exploding joins, unnecessary UNION, spilling and poor pruning from what the profile shows
- Use Query Insights to find detected problems and the fix Snowflake recommends for each
- Choose a query rewrite, a larger warehouse, or an optimization such as search optimization, materialized views, clustering or query acceleration to fix the root cause
1.Opening the Query Profile
Query History tells you a statement was slow. The Query Profile tells you why. It shows the statement's execution plan as a tree of operator nodes, such as TableScan, Filter, Join, Aggregate, Sort and UnionAll, with statistics for each one. The Most Expensive Nodes pane lists the operators that took the longest. You can drill further into what percentage of a node's time went to each category of processing. Start every investigation with the top node in that pane, not with the SQL text.
Checkpoint 1 of 5· Put it in order
Put the steps for opening a query's Query Profile in Snowsight in order.
- 1.In the navigation menu, select Monitoring » Query History
- 2.Select the query ID of a query
- 3.Sign in to Snowsight
- 4.Select the Query Profile tab
The profile is opened from a specific query in Query History: find the query, select its ID, then open the Query Profile tab.
“In the navigation menu, select Monitoring » Query History.”Source: docs.snowflake.com
Two groups of statistics in the profile matter most for troubleshooting. Pruning shows *Partitions scanned* (how many micro-partitions were read) against *Partitions total* (how many the table has). Spilling shows *Bytes spilled to local storage* and *Bytes spilled to remote storage*, which appear when intermediate results do not fit in memory. To analyse these numbers in SQL instead of on screen, call the GET_QUERY_OPERATOR_STATS function. It returns the same per-operator performance statistics.
2.Four root causes the profile reveals
The Snowflake documentation describes four common problems you can find from operator statistics.
Exploding joins. A join with no condition produces a Cartesian product. A condition where rows in one table match many rows in the other has a similar effect. Either way, the Join operator outputs far more rows than it takes in, often by orders of magnitude, and it usually also uses a lot of time. Compare the Join node's output row count with its inputs.
UNION where UNION ALL would do. UNION ALL simply concatenates. UNION also removes duplicates, which shows up as an Aggregate on top of a UnionAll. If the inputs cannot contain duplicates, that Aggregate is wasted work.
Spilling. When memory cannot hold intermediate results, for example when removing duplicates from a huge data set, the engine spills to local disk. If local disk runs out, it spills to remote disk. This can slow a query down a lot, especially when the spill goes to remote disk. The recommended fixes are a larger warehouse, which gives more memory and local disk, and/or processing the data in smaller batches. An exploding join often causes spilling, because the large intermediate result is what overflows memory. Fixing the join condition removes both problems.
Poor pruning. Snowflake can skip micro-partitions based on the query's filters, but only when the order the data is stored in matches the filtered columns. If Partitions scanned is a small fraction of Partitions total, pruning worked. If it is close to the total and a Filter above the TableScan removes many rows, a different data organization might help this query.
Checkpoint 2 of 5· Check yourself
In a Query Profile, a TableScan reports Partitions scanned = 9,800 of Partitions total = 10,000. A Filter operator directly above it discards most of the rows. What does this indicate?
Efficient pruning means scanned is a small fraction of total. Here almost every partition was read and then filtered, which means the stored data order does not match the filter.
“this might signal that a different data organization might be beneficial for this query”Source: docs.snowflake.com
Checkpoint 3 of 5· Exam question
During a performance review, a query's Query Profile shows a very large value for 'Bytes spilled to remote storage,' and the query still runs slowly even after moving it to a next-size-up warehouse. What should the data engineer conclude and try next?
Correct answer: A — The intermediate data is too large for one resize to fix, so the spilling operator should be shrunk by filtering earlier, since remote spilling is costlier than local spilling
- A. Correct: remote spilling means intermediate results overflowed both memory and local disk, which is markedly slower than local spilling, so the engineer needs to shrink the working set in addition to or instead of resizing once.
- B. Incorrect: the local disk cache holds previously scanned table data, not spilled intermediate results, and re-running the query does not make a spill metric from real memory pressure go away.
- C. Incorrect: a Cartesian join is one possible cause of a huge intermediate result, but remote spilling can also come from large legitimate joins, sorts, or aggregations, so it is not a guaranteed signal.
- D. Incorrect: the result cache only serves an identical repeated query with unchanged underlying data by skipping execution entirely; it does nothing to reduce memory pressure while a query is actively processing.
Sources2
3.Letting Query Insights name the problem
You don't always have to read the operator tree yourself. When Snowflake detects a condition that affects performance, it records a query insight. Each insight explains the condition, identifies the part of the query that caused it, and suggests a next step. In the Query Profile tab, nodes with insights are highlighted, and the Query Insights pane lists each instance. Select View for details. To find insights across many queries, query the QUERY_INSIGHTS view.
| Type ID | Condition | Recommended next step |
|---|---|---|
| QUERY_INSIGHT_NO_FILTER_ON_TOP_OF_TABLE_SCAN | No WHERE clause; the whole table is scanned | Add a WHERE clause |
| QUERY_INSIGHT_UNSELECTIVE_FILTER | WHERE clause removes some rows, but not many | Make the condition more selective |
| QUERY_INSIGHT_LIKE_WITH_LEADING_WILDCARD | LIKE pattern starts with a wildcard | Avoid the leading wildcard, or consider search optimization |
| QUERY_INSIGHT_JOIN_WITH_NO_JOIN_CONDITION | Join missing its condition, producing a cross join | Specify one or more join conditions |
| QUERY_INSIGHT_EXPLODING_JOIN | Join returns many more rows than the joined tables contain | Add or change the join condition |
| QUERY_INSIGHT_UNNECESSARY_UNION_DISTINCT | UNION used on disjoint inputs | Use UNION ALL |
| QUERY_INSIGHT_REMOTE_SPILLAGE | Warehouse spilled data to storage | Larger warehouse, or process data in smaller batches |
| QUERY_INSIGHT_QUEUED_OVERLOAD | Query waited in the warehouse queue too long | Larger warehouse, or one with fewer concurrent queries |
Insights don't cover every query. They are produced for SQL queries against databases that are processed by warehouses. They are not produced for queries whose plan takes multiple steps, queries on secure objects, hybrid tables or interactive tables, Native App queries, EXPLAIN, or queries that reuse results. The "filter not selective" insight is also not produced for queries accelerated by the query acceleration service. If the pane is empty, that does not mean the query is efficient.
Checkpoint 4 of 5· Match them up
Match each insight to the recommended fix.
Tap a term, then the definition that fits it.
Each insight comes with a fixed recommendation. Queue and spill problems point to warehouse capacity. Join, UNION and filter problems point to changing the SQL.
“To improve performance, use UNION ALL, rather than UNION [ DISTINCT ].”Source: docs.snowflake.com
Sources3
4.Increasing efficiency: rewrite first, then optimize
Once you know the root cause, the fix follows from it. Fix problems in the SQL first, because those fixes cost nothing to run: add or tighten WHERE clauses, add the missing join condition, use UNION ALL for disjoint inputs, and remove a DISTINCT or GROUP BY that doesn't change the result. Spill and queue problems are about capacity, so the fix is a larger warehouse, smaller batches, or less concurrency.
When the query is already reasonable but still has to scan or search too much data, Snowflake offers four optimizations. Each suits different query shapes, and each adds cost. Search optimization, materialized views and clustering add both storage and compute costs.
| Optimization | Query types it helps | Note |
|---|---|---|
| Search optimization service | Equality, substring/regex, text and IP, VARIANT and structured-type elements, GEOGRAPHY searches | Can prune micro-partitions before query acceleration handles the rest |
| Query acceleration service | Queries with filters or aggregation; with LIMIT, must also have ORDER BY | Suits ad-hoc analytics, unpredictable data volume, large scans with selective filters |
| Materialized views | Equality, range, sort | Helps only the rows and columns in the view; can define a different clustering key on the same source table |
| Clustering the table | Equality, range | A table can be clustered on only one key (one or more columns or expressions) |
These options can be combined. Search optimization and query acceleration work together: search optimization first prunes the micro-partitions a query doesn't need, then query acceleration offloads part of the remaining work to shared compute resources. Whatever you change, go back to QUERY_HISTORY afterwards and compare the pattern's elapsed time and partitions scanned with the earlier runs to confirm it helped.
Checkpoint 5 of 5· Check yourself
A table is already clustered on order_date for range reports. A second, frequent workload filters the same table by customer_id and is pruning poorly. Which option lets that workload have its own clustering without changing the table's key?
A table can have only one clustering key. A materialized view can define a different clustering key on the same source table. A larger warehouse or query acceleration adds compute but doesn't change how the data is organized.
“You can also use materialized views to define different clustering keys on the same source table”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A query that spills to remote storage should be fixed with clustering or search optimization.Why is that wrong?
Spilling happens when memory cannot hold intermediate results. The documented fixes are a larger warehouse and/or processing the data in smaller batches. If an exploding join is producing the oversized result, fix the join.
Covered in Four root causes the profile reveals
2.If the Query Insights pane is empty, the query has no performance problems.Why is that wrong?
Several kinds of query never get insights, including multi-step plans, queries on secure objects or hybrid tables, EXPLAIN, and queries that reuse results.
Covered in Letting Query Insights name the problem
3.You can add a separate clustering key to a table for each workload that filters it differently.Why is that wrong?
A table has only one clustering key, though it can include several columns or expressions. For a second access pattern, use a materialized view with its own clustering key.
Covered in Increasing efficiency: rewrite first, then optimize
Practise it for real
Go from warehouse-level telemetry to the root cause of one costly query pattern
1.In a worksheet with access to SNOWFLAKE.ACCOUNT_USAGE, group QUERY_HISTORY by query_hash for one warehouse over the last 7 days, ordered by SUM(total_elapsed_time) DESC, returning ANY_VALUE(query_id).
Why: This ranks query patterns by total time across all runs, not by a single run.
You should see: Up to 100 rows. The top rows are the patterns that used the most time in total.
2.Copy the query_id from the top row, open Monitoring » Query History in Snowsight, filter by that Query ID, and open the Query Profile tab.
Why: Moves from statement-level telemetry to the operator tree.
You should see: The execution plan with a Most Expensive Nodes pane.
3.In the most expensive node and the TableScan nodes, compare Partitions scanned with Partitions total, check the Spilling statistics, and compare Join output rows with Join input rows.
Why: These three readings separate poor pruning, memory pressure and an exploding join.
You should see: At least one of these stands out, or none does, which suggests looking at queueing instead.
4.Read the Query Insights pane for any highlighted nodes and select View on each entry.
Why: Snowflake may already have identified the condition and suggested a fix.
You should see: Zero or more insights with recommended next steps, or none if the query falls into a category that insights don't cover.
Stuck? Get a nudge
If execution looks fine but total_elapsed_time is high, look at queued_overload_time and queued_provisioning_time before changing any SQL.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“It includes a Most Expensive Nodes pane that identifies the operator nodes that are taking the longest to execute.”
↩︎ Opening the Query Profile“You can programmatically access the performance statistics of the Query Profile by executing the GET_QUERY_OPERATOR_STATS function.”
↩︎ Opening the Query Profile“In the navigation menu, select Monitoring » Query History.”
↩︎ Checkpoint - 2.
“Spilling — information about disk usage for operations where intermediate results do not fit in memory”
↩︎ Opening the Query Profile“the Join operator produces significantly (often by orders of magnitude) more tuples than it consumes.”
↩︎ Four root causes the profile reveals“If the local disk space is not sufficient, the spilled data is then saved to remote disks.”
↩︎ Four root causes the profile reveals“If the former is a small fraction of the latter, pruning is efficient.”
↩︎ Four root causes the profile reveals“the data storage order needs to be correlated with the query filter attributes.”
↩︎ Four root causes the profile reveals“Using a larger warehouse (effectively increasing the available memory/local disk space for the operation), and/or”
↩︎ Exam trap 1“These queries show in Query Profile as a UnionAll operator with an extra Aggregate operator on top”
↩︎ Prediction“this might signal that a different data organization might be beneficial for this query”
↩︎ Checkpoint - 3.
“Each insight includes a message that explains how query performance might be affected and provides a general recommendation for improving performance.”
↩︎ Letting Query Insights name the problem“If you need to specify a pattern that starts with a wildcard, consider enabling search optimization for more efficient pattern matching.”
↩︎ Letting Query Insights name the problem“The nodes that have corresponding insights are highlighted.”
↩︎ Letting Query Insights name the problem“To improve performance, remove the unnecessary DISTINCT or GROUP BY clause.”
↩︎ Increasing efficiency: rewrite first, then optimize“Queries that reuse results.”
↩︎ Exam trap 2“To improve performance, use UNION ALL, rather than UNION [ DISTINCT ].”
↩︎ Checkpoint - 4.
“Materialized views improve performance only for the subset of rows and columns included in the materialized view.”
↩︎ Increasing efficiency: rewrite first, then optimize“First, search optimization can prune the micro-partitions that aren’t needed for a query.”
↩︎ Increasing efficiency: rewrite first, then optimize“Query acceleration works well with ad-hoc analytics, queries with unpredictable data volume, and queries with large scans and selective filters.”
↩︎ Increasing efficiency: rewrite first, then optimize“A table can be clustered only on a single key, which can contain one or more columns or expressions.”
↩︎ Exam trap 3“You can also use materialized views to define different clustering keys on the same source table”
↩︎ Checkpoint