Performance Monitoring and Tuning · Topic 4 of 16
Troubleshooting Spilling, Pruning, Timeouts and EXPLAIN
Troubleshoot slow Snowflake queries: data spilling, missing partition pruning, the timeout parameters and how to read an EXPLAIN plan without running it.
- 5 min
- Spilling goes local first, then remote; fix with a larger warehouse or smaller batches.
- Pruning is judged by partitions scanned against partitions total; fix with clustering and plain filters.
STATEMENT_TIMEOUT_IN_SECONDSdefault is 172800;STATEMENT_QUEUED_TIMEOUT_IN_SECONDSdefault is 0;LOCK_TIMEOUTdefault is 43200.
What it is
Most slow query scenarios on the exam fall into a few families: the warehouse ran out of memory and spilled, the query scanned far too many micro partitions because pruning did not work, queries waited in a queue, or a runaway statement ran too long and a timeout parameter was needed. EXPLAIN lets you inspect the plan without running the query.
How it works
Troubleshooting map
| Symptom in the profile or history | Likely cause | Typical fix |
|---|---|---|
| Bytes spilled to local or remote storage | Operator needs more memory than the warehouse has | Larger warehouse, process in smaller batches, reduce data earlier |
| Partitions scanned close to partitions total | Filter does not prune | Filter on the ordering column, add or fix a clustering key, avoid functions on the column |
| High queued overload time | Too many concurrent queries | Multi cluster warehouse, separate warehouses, schedule work |
| Join output far above input | Join explosion | Fix the join condition |
| Long compilation time | Very complex SQL | Simplify views and statements |
| Query never ends | Runaway statement | Set STATEMENT_TIMEOUT_IN_SECONDS |
Data spilling
When intermediate results of an operation such as a sort, aggregate or join do not fit in warehouse memory, Snowflake spills to local disk. If local disk is also insufficient, it spills to remote storage, which is the slowest case. The Query Profile reports both under the Spilling statistics, and QUERY_HISTORY has BYTES_SPILLED_TO_LOCAL_STORAGE and BYTES_SPILLED_TO_REMOTE_STORAGE.
Solutions, in order of how often the exam expects them:
- Use a larger warehouse, which has more memory and local disk per cluster.
- Process in smaller batches or add filters so each operation handles less data.
- For very memory heavy work, a Snowpark optimized warehouse provides much more memory per node.
Remote spilling hurts most. A scenario with large remote spill bytes usually wants a bigger warehouse, not a multi cluster one, because spilling is a per query memory problem, not a concurrency problem.
Data pruning not happening
Snowflake stores tables in micro partitions with min and max metadata per column. A filter lets the optimizer skip partitions whose range cannot match. If Partitions scanned is close to Partitions total, pruning did not help.
Common reasons and fixes:
- The filter column is not correlated with how data is stored. Define a clustering key on the filter column, or load data ordered by it.
- The filter wraps the column in a function or casts it, which hides the raw range.
- The table is large and clustering has drifted. Check with
SYSTEM$CLUSTERING_INFORMATIONand consider automatic clustering. - The query filters on a high cardinality column such as an ID, where search optimization may suit better than clustering.
SELECT SYSTEM$CLUSTERING_INFORMATION('sales.public.orders', '(order_date)');
Timeout parameters
| Parameter | Level | Default | Purpose |
|---|---|---|---|
STATEMENT_TIMEOUT_IN_SECONDS | Account, session, object (warehouse) | 172800 (2 days) | Cancels a statement that runs too long |
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS | Account, session, warehouse | 0 (no timeout) | Cancels a statement that waits in the queue too long |
LOCK_TIMEOUT | Session | 43200 (12 hours) | How long a statement waits for a lock |
USER_TASK_TIMEOUT_MS | Task | 3600000 (1 hour) | Maximum run time of a task |
ABORT_DETACHED_QUERY | Session | FALSE | Aborts running queries about 5 minutes after client heartbeats stop |
SESSION_IDLE_TIMEOUT_MINS | Session policy | 240 | Ends an idle programmatic session |
When a statement timeout is set for both the warehouse and the session (or user or account), the lower value is the one that applies. A common pattern is a short statement timeout on a dashboard warehouse so one bad query cannot hold it for hours.
ALTER WAREHOUSE bi_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 600
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 120;
Use an explain plan
EXPLAIN shows the logical plan Snowflake would use, without executing the statement. Output formats are TABULAR (default), JSON and TEXT.
EXPLAIN USING TEXT
SELECT c.name, SUM(o.amount)
FROM orders o JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.name;
The plan lists operations such as TableScan, Join, Aggregate and Result, with columns including partitionsTotal, partitionsAssigned and bytesAssigned. A large gap between total and assigned partitions shows pruning will work; a small gap shows it will not. Use it to check a query before running something expensive.
Common mistakes
Adding clusters to cure spilling. Extra clusters help concurrency, not memory per query.
Raising STATEMENT_TIMEOUT_IN_SECONDS to hide slow queries. It only controls when a statement is cancelled.
Expecting EXPLAIN to show actual runtime numbers. It shows the plan, not measured times.
Filtering on a function of the column. The raw min and max no longer match the predicate, so pruning is lost.
What to remember for the exam
- Spilling goes local first, then remote; fix with a larger warehouse or smaller batches.
- Pruning is judged by partitions scanned against partitions total; fix with clustering and plain filters.
STATEMENT_TIMEOUT_IN_SECONDSdefault is 172800;STATEMENT_QUEUED_TIMEOUT_IN_SECONDSdefault is 0;LOCK_TIMEOUTdefault is 43200.- For the statement timeouts, the lower of the warehouse and session values applies.
EXPLAINdoes not run the query.
Check yourself
Remote spill bytes are very high for one big query. What first?
Run it on a larger warehouse or process the data in smaller batches.
Partitions scanned equals partitions total on a filtered query. What does that mean?
Pruning is not happening, so check the filter column, functions on it and clustering.
Does
EXPLAINexecute the statement?No, it only returns the plan, including partitions assigned.
Topic 4 of 16 in Performance Monitoring and Tuning
Test yourself on Performance Monitoring and Tuning
The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.