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
In a nutshell
  • 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_SECONDS default is 172800; STATEMENT_QUEUED_TIMEOUT_IN_SECONDS default is 0; LOCK_TIMEOUT default 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.

Symptomslow or stuckcheckQuery Profilespill, pruning, queuematchCausememory, scan, concurrencyapplyFixresize, cluster, rewrite, timeout

How it works

Troubleshooting map

Symptom in the profile or historyLikely causeTypical fix
Bytes spilled to local or remote storageOperator needs more memory than the warehouse hasLarger warehouse, process in smaller batches, reduce data earlier
Partitions scanned close to partitions totalFilter does not pruneFilter on the ordering column, add or fix a clustering key, avoid functions on the column
High queued overload timeToo many concurrent queriesMulti cluster warehouse, separate warehouses, schedule work
Join output far above inputJoin explosionFix the join condition
Long compilation timeVery complex SQLSimplify views and statements
Query never endsRunaway statementSet 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:

  1. Use a larger warehouse, which has more memory and local disk per cluster.
  2. Process in smaller batches or add filters so each operation handles less data.
  3. For very memory heavy work, a Snowpark optimized warehouse provides much more memory per node.
Watch out

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_INFORMATION and 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

ParameterLevelDefaultPurpose
STATEMENT_TIMEOUT_IN_SECONDSAccount, session, object (warehouse)172800 (2 days)Cancels a statement that runs too long
STATEMENT_QUEUED_TIMEOUT_IN_SECONDSAccount, session, warehouse0 (no timeout)Cancels a statement that waits in the queue too long
LOCK_TIMEOUTSession43200 (12 hours)How long a statement waits for a lock
USER_TASK_TIMEOUT_MSTask3600000 (1 hour)Maximum run time of a task
ABORT_DETACHED_QUERYSessionFALSEAborts running queries about 5 minutes after client heartbeats stop
SESSION_IDLE_TIMEOUT_MINSSession policy240Ends 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.

StatementSQL textEXPLAINPlanoperations, partitions assignedjudgeDecisionrun, rewrite or cluster

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_SECONDS default is 172800; STATEMENT_QUEUED_TIMEOUT_IN_SECONDS default is 0; LOCK_TIMEOUT default is 43200.
  • For the statement timeouts, the lower of the warehouse and session values applies.
  • EXPLAIN does 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 EXPLAIN execute 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.