What you will be able to do
- Open the Query Profile for any past query and find its most expensive operator nodes and insights
- Recognise local and remote spillage and choose Snowflake's recommended fix
- Detect inefficient pruning from partition statistics and table-scan insights
- Identify an exploding join and say which join condition to fix
Key concept
Query Profile — The Query Profile is a per-query view of the execution plan. It breaks the query into operator nodes and shows statistics for each one, so you can find the step that is costing the most time instead of guessing at the SQL as a whole.
1.Opening the Query Profile and its insights
To evaluate a slow query, start with its Query Profile. The profile has a Most Expensive Nodes pane that lists the operator nodes taking the longest to execute. You can then see what share of each node's time went to a particular category of processing.
The profile belongs to the query, not to the session that ran it. You reach it from Query History in Snowsight, so you can still open it after the user's worksheet has closed. The path is: sign in to Snowsight, choose Monitoring » Query History, select the query ID, then open the Query Profile tab. To get the same operator statistics programmatically, call the GET_QUERY_OPERATOR_STATS function.
Checkpoint 1 of 6· Put it in order
Put the steps for opening a past query's Query Profile in order.
- 1.Select the Query Profile tab
- 2.Sign in to Snowsight
- 3.Select the query ID of the query
- 4.In the navigation menu, select Monitoring » Query History
The Query Profile is opened from a query's entry in Query History, not from the worksheet or session that ran it.
“In the navigation menu, select Monitoring » Query History.”Source: docs.snowflake.com
On the same tab, Snowflake shows query insights. These are conditions it detected that can affect performance. The nodes involved are highlighted, and a Query Insights pane lists each instance. Every insight comes with a message, details of the part of the query that caused it, and a suggested next step when the condition hurts performance. The same insights are available in SQL through the SNOWFLAKE.ACCOUNT_USAGE.QUERY_INSIGHTS view, which has one row per insight. Each row has an insight_topic label such as TABLE_SCAN, JOIN or WAREHOUSE. Latency for that view may be up to 90 minutes.
Insights are not produced for every query. They are produced only for SQL queries against databases that are processed by warehouses. Queries that reuse results, queries involving secure objects, EXPLAIN queries, hybrid-table queries and a few other cases get none.
Checkpoint 2 of 6· Check yourself
An analyst opens the Query Profile of a query that was answered from a reused result and finds no Query Insights. What is the most likely explanation?
The limitations list queries that reuse results among the cases that get no insights. The 90-minute latency applies to the QUERY_INSIGHTS view, not to the profile.
“Insights are produced for SQL queries that are made against databases and are processed by warehouses.”Source: docs.snowflake.com
2.Bytes spilled to local and remote storage
Spillage is a memory problem. When a warehouse runs out of memory during a query, the intermediate data spills to local disk. If the query needs even more memory, it spills further, to remote cloud-provider storage. The profile reports both figures, Bytes spilled to local storage and Bytes spilled to remote storage, and QUERY_HISTORY records them as bytes_spilled_to_local_storage and bytes_spilled_to_remote_storage. Remote spill is the worse of the two, and it is the one that triggers the QUERY_INSIGHT_REMOTE_SPILLAGE insight.
Snowflake recommends two fixes. The first is a larger warehouse, which gives the operation more memory and local storage. If that isn't an option, process the data in smaller batches. Use the Query Profile to find which operator nodes are spilling. The query below finds the worst offenders across the account. Note the order of its ORDER BY columns: remote spill comes first.
SELECT query_id, SUBSTR(query_text, 1, 50) partial_query_text, user_name, warehouse_name,
bytes_spilled_to_local_storage, bytes_spilled_to_remote_storage
FROM snowflake.account_usage.query_history
WHERE (bytes_spilled_to_local_storage > 0
OR bytes_spilled_to_remote_storage > 0 )
AND start_time::date > dateadd('days', -45, current_date)
ORDER BY bytes_spilled_to_remote_storage, bytes_spilled_to_local_storage DESC
LIMIT 10;Checkpoint 3 of 6· Exam question
An analyst runs a large GROUP BY aggregation on an X-Small warehouse. The Query Profile statistics pane reports 40 GB under "Bytes spilled to local storage" and the Aggregate node dominates execution time. What is the most effective first change to try?
Correct answer: A — Resize the warehouse to a larger size so more memory becomes available for the aggregation step to run in.
- A. Local spilling happens when the intermediate working set does not fit in the warehouse's allocated memory, so increasing the warehouse size gives the aggregation more memory and directly reduces spilling. This is the standard first remedy Snowflake documentation recommends for spill-heavy queries.
- B. Multi-cluster warehouses add clusters to handle concurrent queries queuing for resources, but they do not give a single query more memory per cluster, so this does not address spilling within one aggregation.
- C. Trimming output columns can shrink the final result set, but the spill here is driven by the aggregation's intermediate working set on the grouping keys, not by the number of returned columns, so this has little effect.
- D. The result cache only helps when an identical query is reissued against unchanged data; it does nothing for the first, still-spilling execution of this aggregation.
Checkpoint 4 of 6· Check yourself
A nightly aggregation spills heavily to remote storage. Budget rules prevent moving it to a larger warehouse. What does Snowflake recommend?
The two documented remedies for spillage are more memory (a larger warehouse) or less data per operation (smaller batches). With the first ruled out, batching is the answer.
“If using a larger warehouse is not an option, change the query to process data in smaller batches.”Source: docs.snowflake.com
Sources4
3.Spotting inefficient pruning
Pruning means skipping micro-partitions that can't contain the rows a query needs. In the Query Profile, a table scan's Pruning statistics show Partitions scanned next to Partitions total. QUERY_HISTORY records the same pair as partitions_scanned and partitions_total. If a query scans nearly all of a table's partitions while returning only a few rows, pruning is not working for it.
Insights in the TABLE_SCAN topic tell you why. Most of them point at the WHERE clause.
| Insight type ID | What Snowflake detected | Suggested direction |
|---|---|---|
| QUERY_INSIGHT_NO_FILTER_ON_TOP_OF_TABLE_SCAN | No WHERE clause, so the entire table is scanned | Add a WHERE clause |
| QUERY_INSIGHT_INAPPLICABLE_FILTER_ON_TABLE_SCAN | A WHERE clause that filters out no rows | Add or tighten a selective condition |
| QUERY_INSIGHT_UNSELECTIVE_FILTER | A WHERE clause that removes some rows but not many | Make the condition more selective |
| QUERY_INSIGHT_LIKE_WITH_LEADING_WILDCARD | A LIKE pattern starting with a wildcard | Avoid the leading wildcard, or consider search optimization |
| QUERY_INSIGHT_FILTER_WITH_CLUSTERING_KEY | The filter used the table's clustering key | None: this insight reports a benefit |
Two details matter here. The "filter not applicable" and "filter not selective" insights are different: the first means no rows were filtered out, and the second means some were, but too few. Also, Snowflake skips the "filter not selective" insight for queries accelerated by the query acceleration service. A missing insight on an accelerated query doesn't prove the filter is good.
To find pruning candidates across a warehouse, compare the two partition columns in QUERY_HISTORY:
SELECT query_id,
ROW_NUMBER() OVER(ORDER BY partitions_scanned DESC) AS query_id_int,
query_text,
total_elapsed_time/1000 AS query_execution_time_seconds,
partitions_scanned,
partitions_total,
FROM snowflake.account_usage.query_history Q
WHERE warehouse_name = 'my_warehouse' AND TO_DATE(Q.start_time) > DATEADD(day,-1,TO_DATE(CURRENT_TIMESTAMP()))
AND total_elapsed_time > 0 --only get queries that actually used compute
AND error_code IS NULL
AND partitions_scanned IS NOT NULL
ORDER BY total_elapsed_time desc
LIMIT 50;Checkpoint 5 of 6· Check yourself
A query's WHERE clause filters out some rows, but the scan still reads far more data than the result needs. Which insight describes this?
"Filter not selective" means the WHERE clause does remove rows, just not enough. "Filter not applicable" means it removes none at all.
“Unlike the Filter not applicable insight, this insight indicates that the WHERE clause is filtering out some rows but it could have been more selective.”Source: docs.snowflake.com
4.Exploding joins
A common SQL mistake is a join with no join condition, which produces a Cartesian product. A similar one is a condition where each record in one table matches many records in the other. Either way, the Join operator produces significantly more tuples than it consumes, often by orders of magnitude. In the Query Profile, you spot this by comparing the number of records a Join operator produces with the number coming into it. The Join operator usually also accounts for a lot of the query's time.
Query insights split this pattern into several types. A join with no condition becomes a cross join that returns every combination of rows. An exploding join (not nested) is a join of two data sets that returns many more rows than the joined tables contain. A nested exploding join is a join that takes the output of another join. Here the fault usually lies in the child joins, so that is where the condition needs fixing. A separate insight flags a complex join condition that is evaluated *after* the data sets are joined. Evaluating it earlier would mean the join processes less data.
In every case the fix is in the join logic: add or correct the join condition. Adding a WHERE clause to a subquery that feeds the join can also reduce the rows. A filter applied several steps after an exploding join hides the problem in the final result, but the join still produces all those extra rows first.
Checkpoint 6 of 6· Match them up
Match each join insight to the condition it reports.
Tap a term, then the definition that fits it.
All four belong to the JOIN topic. They differ in where the excess rows come from, and that tells you which condition to change.
“A join that includes the output of at least one other join is returning many more rows than are in the tables being joined.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Any nonzero bytes_spilled_to_remote_storage means the warehouse ran out of memory and needs resizing.Why is that wrong?
When the query acceleration service is enabled, Snowflake writes a small amount of data to remote storage for every eligible query, so a small nonzero value is expected.
Covered in Bytes spilled to local and remote storage
2.If a later Filter removes the extra rows from an exploding join, the result is correct and nothing needs fixing.Why is that wrong?
The join still produces the extra rows before the filter runs. The fix is to add or change the join condition, or to filter a subquery that feeds the join.
Covered in Exploding joins
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 and its insights“You can programmatically access the performance statistics of the Query Profile by executing the GET_QUERY_OPERATOR_STATS function.”
↩︎ Opening the Query Profile and its insights“The Query Profile allows you to examine which parts of a query are taking the longest to execute.”
↩︎ Key concept“In the navigation menu, select Monitoring » Query History.”
↩︎ Checkpoint - 2.
“In Query Profile tab under Query History, you can view the insights for a query. The nodes that have corresponding insights are highlighted.”
↩︎ Opening the Query Profile and its insights“A query or subquery has no WHERE clause, which means that the query scans an entire table and might return more rows than intended.”
↩︎ Spotting inefficient pruning“Snowflake does not produce the “filter not selective” insight for queries that are accelerated by the query acceleration service.”
↩︎ Spotting inefficient pruning“The join contains a complex join condition that is evaluated after the data sets are joined.”
↩︎ Exploding joins“To prevent the join from producing more rows than are in the tables being joined, add or change the join condition.”
↩︎ Exam trap 2“Insights are produced for SQL queries that are made against databases and are processed by warehouses.”
↩︎ Checkpoint“If using a larger warehouse is not an option, change the query to process data in smaller batches.”
↩︎ Checkpoint“Unlike the Filter not applicable insight, this insight indicates that the WHERE clause is filtering out some rows but it could have been more selective.”
↩︎ Checkpoint“A join that includes the output of at least one other join is returning many more rows than are in the tables being joined.”
↩︎ Checkpoint - 3.
“Latency for the view may be up to 90 minutes.”
↩︎ Opening the Query Profile and its insights - 4.
“Performance degrades drastically when a warehouse runs out of memory while executing a query because memory bytes must “spill” onto local disk storage.”
↩︎ Bytes spilled to local and remote storage“If the query requires even more memory, it spills onto remote cloud-provider storage, which results in even worse performance.”
↩︎ Bytes spilled to local and remote storage“You can use the Query Profile to identify which operation nodes are causing data to spill to storage.”
↩︎ Bytes spilled to local and remote storage“Therefore, don’t be concerned by a nonzero value for bytes_spilled_to_remote_storage in the QUERY_HISTORY view when QAS is enabled.”
↩︎ Exam trap 1 - 5.
“Partitions scanned — number of partitions scanned so far. Partitions total — total number of partitions in a given table.”
↩︎ Spotting inefficient pruning“For such queries, the Join operator produces significantly (often by orders of magnitude) more tuples than it consumes.”
↩︎ Exploding joins“This can be observed by looking at the number of records produced by a Join operator”
↩︎ Exploding joins