COF-C03 Question 259
Single answerBytes spilled to storageA data engineering team notices that a nightly aggregation query is taking much longer than expected on a MEDIUM warehouse. In the Query Profile, the operator details show a high value for 'Bytes spilled to local storage' and an even higher value for 'Bytes spilled to remote storage' during a large sort and aggregation step. The SQL logic is correct and must process the full data set. Which action is the BEST next step to reduce the query runtime?
- A
Increase the warehouse size so the query has more memory available during execution
- B
Enable Search Optimization Service on the source table to eliminate spill during sorting
- C
Convert the source table to a temporary table so intermediate results stay in memory
- D
Suspend and resume the warehouse before running the query to clear the local disk cache
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, 'bytes spilled to local storage' and 'bytes spilled to remote storage' in the Query Profile are indicators that an operator exceeded the memory available to it during execution. This commonly occurs with large sorts, joins, window functions, or aggregations. Spilling to local storage is slower than staying in memory, and spilling to remote storage is slower still. When the SQL must process the full data set and the main symptom is heavy spill, the best immediate action is often to scale up the warehouse to provide more memory. Snowflake documentation and performance tuning guidance emphasize using the Query Profile to identify bottlenecks and considering warehouse resizing for memory-bound workloads. Other options in this scenario address different performance patterns or reflect common misconceptions: Search Optimization is for specific query access patterns, table type does not control execution memory, and clearing cache does not eliminate spill.
- A. Correct.
Correct. Bytes spilled to local or remote storage typically indicate the query needed more working memory than was available for operations such as sorts, joins, or aggregations. Remote spill is especially expensive and usually has a greater performance impact than local spill. A common and appropriate remediation is to increase the warehouse size, which provides more compute resources and memory per cluster, reducing the likelihood of spill and often improving runtime for memory-intensive queries.
- B. Incorrect.
Incorrect. Search Optimization Service can improve selective point-lookups and certain predicate-based access patterns, but it does not specifically address insufficient working memory for sort or aggregation operators. It is not the primary solution for spill caused by memory-intensive execution steps.
- C. Incorrect.
Incorrect. Temporary tables are session-scoped objects, but converting a source table to a temporary table does not cause large sort or aggregation operations to remain in memory. Query execution memory management is governed by the warehouse resources available, not by whether the table is permanent or temporary.
- D. Incorrect.
Incorrect. Suspending and resuming a warehouse clears cached data, which can actually remove a potential performance benefit. Spill to storage is related to memory pressure during query execution, not stale local disk cache. Clearing cache does not solve the underlying issue of insufficient memory for the operator.