Snowflake Query Profile, EXPLAIN Plans and Troubleshooting Slow Queries

1.Anatomy of a Query Profile: steps, tree, nodes and operator types

Query history tells you which queries are slow. The Query Profile tells you why. To open it in Snowsight, go to Monitoring » Query History, select a query ID, and then select the Query Profile tab. The profile shows the query as a tree of operators. In that tree a parent operator sits above its child, with a link between them, and rows flow upward from the scans at the bottom to the Result at the top. Most queries run as a single step. Some are executed as several distinct steps, and each step has its own operator tree.

Each box in the tree is an operator node. The Most Expensive Nodes pane lists the nodes that take the longest to execute. For each node you can see what percentage of its time went to each category of processing. To get the same statistics in SQL instead of the UI, call the GET_QUERY_OPERATOR_STATS function.

Sources123

2.EXPLAIN: the plan before it runs, and how to articulate the execution path

The Query Profile describes a query that has already run. EXPLAIN works on a statement before it runs. It returns the logical execution plan, meaning the operations Snowflake would perform, such as table scans and joins. You get it without paying to run the query. The output can be TABULAR (the default), JSON or TEXT. JSON is the easiest format to store in a table and query.

EXPLAIN [ USING { TABULAR | JSON | TEXT } ] <statement>

To articulate the execution path of a query, read the plan from the bottom up. The leaves are scan operators such as TableScan. They sit at the bottom of the tree and are the first step in reading the data the query needs. Each row of the plan names its parent in parentOperators, so you can follow every branch upward until it reaches the Result operator at id 0. Take the documented plan for SELECT Z1.ID, Z2.ID FROM Z1, Z2 WHERE Z2.ID = Z1.ID, shown below. Z2 is scanned (id 2) and feeds the InnerJoin directly. Z1 is scanned (id 4) and passes through a JoinFilter (id 3) first, which removes rows that cannot match the join. Both branches meet at the InnerJoin (id 1) on joinKey (Z2.ID = Z1.ID), and the InnerJoin feeds Result (id 0).

Be precise about what this path means. The EXPLAIN plan is the logical plan. It shows which operations will be performed and how they relate to each other. The actual execution order does not necessarily match the logical order the plan shows.

That difference is also the line between compile-time and runtime optimization. EXPLAIN compiles the statement but does not execute it, so it needs no running warehouse. The compiler does use Cloud Services credits. The plan records compile-time decisions: the operator tree, the join expressions, the objects to scan, and partitionsAssigned, which is the number of partitions left after compile-time pruning. The partitionsAssigned and bytesAssigned values are upper-bound estimates. At runtime, optimizations such as join pruning can reduce the partitions and bytes actually scanned. The plan can also differ with the size of the current warehouse. The Query Profile then adds what actually happened at runtime: time per node, rows produced, spilling and partitions scanned. Spilling and real pruning effectiveness appear only in that runtime view.

Sources14

3.Troubleshoot common query performance issues: joins, unions and grouping

Two common query-writing mistakes are easy to see in the profile. The first is a bad join condition. Joining without a condition produces a Cartesian product. A condition where one row matches many rows on the other side does something similar. In both cases the Join operator outputs far more rows than it takes in, often by orders of magnitude, and it usually takes a lot of time as well. Compare the rows going into the Join node with the rows coming out of it.

The second mistake is using UNION when UNION ALL would give the right answer. UNION ALL just concatenates its inputs. UNION also removes duplicates. In the profile this shows as a UnionAll operator with an extra Aggregate operator on top. If duplicates cannot occur, or do not matter, that Aggregate is wasted work.

Grouping, sorting and ordering each show up as their own operators, so you can see what they cost. GROUP BY and SELECT DISTINCT appear as an Aggregate node, GROUPING SETS, ROLLUP and CUBE as GroupingSets, window functions as WindowFunction, ORDER BY as Sort, and ORDER BY ... LIMIT as SortWithLimit. A DISTINCT or GROUP BY that returns the same number of rows as the query without it adds a processing step with no effect on the result, so remove it. When a GROUP BY is on a high-cardinality expression, the query acceleration service may not be able to help the query. For repeated range searches or sort operations, materialized views are a documented option. For "most recent N rows" queries (ORDER BY plus LIMIT) on interactive warehouses, the documentation advises making the clustering key match the ORDER BY column so pruning works.

To troubleshoot common query performance issues, follow a fixed routine. Open the Query Profile and check the Query Insights pane. Snowflake highlights the nodes that have insights, and selecting View on an entry shows the condition it detected and the recommended next steps. The same insights are in the SNOWFLAKE.ACCOUNT_USAGE.QUERY_INSIGHTS view, one row per insight, with a latency of up to 90 minutes. Insights are not produced for every query. EXPLAIN queries, queries that reuse results, multi-step queries and queries against hybrid tables do not get them.

Use effective joining conditions. A join with no condition becomes a cross join that returns every possible combination of rows. A complex join condition that can only be evaluated after the join is less efficient than one evaluated before it. Non-equality join predicates can be significantly slower and should be avoided if possible. To find an exploding join in SQL, divide each Join operator's output_rows by its input_rows with GET_QUERY_OPERATOR_STATS, and then check the join condition of any operator with a large ratio.

SELECT  operator_id,
        operator_attributes,
        operator_statistics:output_rows / operator_statistics:input_rows AS row_multiple
  FROM TABLE(GET_QUERY_OPERATOR_STATS($lid))
  WHERE operator_type = 'Join'
  ORDER BY step_id, operator_id;

Sources3567

4.Data spilling and caching: impact and solutions

Performance drops sharply when a warehouse runs out of memory during a query. Data that no longer fits in memory spills to local disk, and if that runs out too, it spills to remote storage. Spilling to remote storage does the most damage. The Query Profile shows which operator nodes are spilling. To find the worst offenders across the account, query QUERY_HISTORY:

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;

Snowflake recommends two fixes. One is a larger warehouse, which gives the operation more memory and local storage. The other is processing the data in smaller batches. Be careful with one false alarm: when the query acceleration service is enabled, Snowflake writes a small amount of data to remote storage for every eligible query. On those warehouses, a nonzero bytes_spilled_to_remote_storage value does not by itself mean a memory problem.

Spilling happens on the warehouse's local disk, and that disk also holds a cache. To compare and contrast the caching techniques Snowflake uses, ask three questions about each one: what is reused, what invalidates it, and what it costs. Each has a different impact on performance.

The result cache (persisted query results) has the biggest effect. If a user repeats a query and the underlying data has not changed, Snowflake does not run the query again. It returns the stored result, so execution is skipped entirely. Reuse requires that the new query matches the previous one exactly. Any difference in syntax, including lowercase versus uppercase keywords or a table alias, prevents full reuse. It also requires that the query uses no non-reusable functions such as RANDOM or UUID_STRING, uses no external functions, and does not select from hybrid tables. The table data and micro-partitions must not have changed, and the role must have the required privileges. A result expires after 24 hours. Each reuse resets that period, up to a maximum of 31 days from the query's first run. Result reuse is on by default and is controlled by the USE_CACHED_RESULT parameter. In the Query Profile, a reused result shows as Query Result Reuse.

The warehouse cache, the local disk cache (the exam guide spells it "local disc"), sits one level lower. A running warehouse keeps a cache of table data that later queries on the same warehouse can read instead of reading from the tables. This helps frequent, similar queries even when the result itself cannot be reused. The percentage_scanned_from_cache statistic, which also appears on a TableScan in GET_QUERY_OPERATOR_STATS, shows how much of a scan came from this cache.

The third kind of reuse is metadata-based results. Some queries, such as SELECT COUNT(*) FROM a table, are computed purely from metadata without accessing any data, and are not processed by a virtual warehouse at all. Materialized views are related but different: they are more flexible than cached results but typically slower.

Suspending or resuming a warehouse affects the local disk cache directly. When a warehouse is suspended, its cache is dropped. When it is resumed, it starts with a cold cache, and the first queries read from table storage again until the cache is rebuilt. Interactive warehouses document the same pattern: when a warehouse was suspended between bursts and resumed, the first queries show higher remote reads while cache warming finishes. So the auto-suspend setting has a direct impact on query performance. Snowflake's guidelines are immediate suspension for tasks, about 5 minutes for DevOps, DataOps and data science work (ad hoc, unique queries benefit less from the cache), and at least 10 minutes for BI and SELECT query warehouses so the cache stays available to users. The other side of the trade-off is that a running warehouse consumes credits even when it is idle. AUTO_SUSPEND is specified in seconds. To check whether a warehouse benefits from its cache, measure the share of data scanned from cache:

SELECT warehouse_name
  ,COUNT(*) AS query_count
  ,SUM(bytes_scanned) AS bytes_scanned
  ,SUM(bytes_scanned*percentage_scanned_from_cache) AS bytes_scanned_from_cache
  ,SUM(bytes_scanned*percentage_scanned_from_cache) / SUM(bytes_scanned) AS percent_scanned_from_cache
FROM snowflake.account_usage.query_history
WHERE start_time >= dateadd(month,-1,current_timestamp())
  AND bytes_scanned > 0
GROUP BY 1
ORDER BY 5;

ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = 600;

Sources8941011312

5.When pruning is not happening

Snowflake stores every table as micro-partitions of 50 to 500 MB of uncompressed data. For each micro-partition it keeps metadata, including the range of values in each column. Pruning uses that metadata to skip micro-partitions a filter cannot match. Ideally, a filter that selects 10% of a range scans only about 10% of the micro-partitions. The closer the scanned fraction is to the fraction of data actually selected, the better the pruning.

Two documented causes of poor pruning are worth knowing. One is the predicate itself: Snowflake does not prune on a predicate that contains a subquery, even when the subquery returns a constant. The other is data order. For pruning to help, the storage order of the data needs to be correlated with the query filter attributes. Data that is unsorted or only partly sorted along the filtered dimension spreads matching values over many micro-partitions. This matters most on very large tables. Pruning also only helps queries that actually filter out a significant amount of data.

To spot poor pruning, compare Partitions scanned with Partitions total in the TableScan operators of the Query Profile. If the former is a small fraction of the latter, pruning is efficient. If the pruning statistics show no data reduction, but a Filter operator above the TableScan removes a number of records, that can signal that a different data organization would benefit the query. The same comparison is available in SQL through partitions_scanned and partitions_total:

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;

Documented ways to improve pruning: if performance degrades over time, the table may benefit from clustering. A materialized view can define a different clustering key on the same source table, or on a subset of it. The search optimization service can prune the micro-partitions that a query does not need. Select only the columns you need and add selective predicates to the WHERE clause. Put literal values in the filter rather than a subquery.

Sources13314

6.Timeout parameters for runaway queries

A hung query keeps using credits until something stops it. There are two timeout parameters to know, and both can be set for the account, a user, a session or a specific warehouse. Both are set at the account level by default. If either is set on both a warehouse and a session, Snowflake enforces the lowest non-zero value.

STATEMENT_TIMEOUT_IN_SECONDS sets the maximum time a SQL statement can run before Snowflake cancels it.

STATEMENT_QUEUED_TIMEOUT_IN_SECONDS sets the maximum time a SQL statement can wait in a warehouse queue before it is canceled. Queued statements do not use credits, but a query that waits too long may no longer be relevant by the time it runs, and running it would waste credits. A value of zero disables the queue timeout.

ALTER ACCOUNT SET STATEMENT_TIMEOUT_IN_SECONDS = <number_of_seconds>;
ALTER USER <username> SET STATEMENT_TIMEOUT_IN_SECONDS = <number_of_seconds>;
ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = <number_of_seconds>;
ALTER WAREHOUSE <warehouse_name> SET STATEMENT_TIMEOUT_IN_SECONDS = <number_of_seconds>;

ALTER ACCOUNT SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <number_of_seconds>;
ALTER USER <username> SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <number_of_seconds>;
ALTER SESSION SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <number_of_seconds>;
ALTER WAREHOUSE <warehouse_name> SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <number_of_seconds>;

Sources15

Exam traps

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

1. 1.Any nonzero bytes_spilled_to_remote_storage means the warehouse is too small.Why is that wrong?

   With the query acceleration service enabled, Snowflake writes a small amount of data to remote storage for every eligible query, even when QAS is not used for it.

   Covered in Data spilling and caching: impact and solutions

2. 2.If a subquery returns a single constant, Snowflake prunes on it just like a literal.Why is that wrong?

   Snowflake never prunes on a predicate that contains a subquery, so the scan reads far more micro-partitions than it needs to.

   Covered in When pruning is not happening

3. 3.A Join node that is slow simply needs a bigger warehouse.Why is that wrong?

   First check whether the Join outputs far more rows than it takes in. That pattern points to a missing or many-to-many join condition, which is a query-writing problem.

   Covered in Troubleshoot common query performance issues: joins, unions and grouping

4. 4.Resuming a suspended warehouse brings back the table data it had cached before.Why is that wrong?

   The warehouse cache is dropped when the warehouse suspends, so a resumed warehouse starts with a cold cache and the first queries read from storage again.

   Covered in Data spilling and caching: impact and solutions

5. 5.A query that is logically identical to an earlier one always hits the result cache, even if it adds a table alias or uses lowercase keywords.Why is that wrong?

   Result reuse requires an exact match of the query text, so a different alias or different keyword case prevents full reuse.

   Covered in Data spilling and caching: impact and solutions

6. 6.The order of operations in an EXPLAIN plan is the exact order in which Snowflake runs them.Why is that wrong?

   EXPLAIN returns a logical plan. It shows the operations and how they relate, but the actual execution order can differ.

   Covered in EXPLAIN: the plan before it runs, and how to articulate the execution path

7. 7.STATEMENT_TIMEOUT_IN_SECONDS also limits how long a statement can wait in a warehouse queue.Why is that wrong?

   Queue time is controlled by a separate parameter, STATEMENT_QUEUED_TIMEOUT_IN_SECONDS. The statement timeout limits how long a statement runs.

   Covered in Timeout parameters for runaway queries