COF-C03 Question 258
Single answerBytes spilled to storageA data engineering team notices that a nightly reporting query has become much slower over the last week. In Query Profile, the operator details show significant bytes spilled to local storage and bytes spilled to remote storage during large sort and aggregation steps. The SQL logic is valid and does not need to change immediately. Which action is the most appropriate first step to reduce the spilling and improve performance?
- A
Increase the size of the virtual warehouse running the query
- B
Convert the permanent table used by the query into a transient table
- C
Enable Search Optimization Service on all tables referenced by the query
- D
Recluster the tables so that fail-safe storage is reduced
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, bytes spilled to local storage and especially bytes spilled to remote storage are indicators that intermediate query processing exceeded available memory on the warehouse. Spilling can occur during memory-intensive operations such as sorts, joins, and aggregations. Remote spilling is generally more expensive than local spilling and is a common sign that the warehouse may be undersized for the workload. When the SQL cannot be changed immediately, the most appropriate first action is often to increase the warehouse size so the query has more memory available. Snowflake documentation and performance tuning guidance emphasize using Query Profile to identify expensive operators and treating spill metrics as signals for either query optimization or additional compute resources.
- A. Correct.
Correct. Bytes spilled to local or remote storage typically indicate that the query needed more memory than was available in the current warehouse for operations such as sorts, hashes, joins, or aggregations. A practical first step is to scale up the virtual warehouse to provide more compute resources and memory, which can reduce or eliminate spilling and improve performance.
- B. Incorrect.
Incorrect. Transient tables reduce data protection costs by removing fail-safe, but they do not increase query execution memory or address bytes spilled during processing. This option confuses storage type/cost management with query execution performance tuning.
- C. Incorrect.
Incorrect. Search Optimization Service can improve performance for highly selective point lookup patterns and certain supported access paths, but it is not the primary first response to memory pressure shown as bytes spilled during large sort and aggregation operations. Applying it to all tables would also be unnecessarily broad and costly.
- D. Incorrect.
Incorrect. Reclustering can improve pruning and scan efficiency for some workloads, but it does not directly reduce fail-safe storage in the way implied here, and fail-safe is unrelated to query spilling. This option mixes table storage maintenance concepts with query memory usage symptoms.