Question 200
A Snowflake administrator investigates the following production situation: A complex aggregation produces unusually high local disk spill and runs slower on a small warehouse than in a representative benchmark. What should the team change first?
Explanation
Correct Answer
The root issue is that the operator's working set exceeds the memory available to the query. The selected fix targets that condition: Review query memory demand and benchmark suitable warehouse sizing or aggregation rewrites. Validate it by checking this outcome: The tuned run reduces spill and meets runtime targets without an excessive credit increase.
Incorrect Answers
This action targets warehouse queue time, where warehouse capacity is insufficient for the active query concurrency. In the given incident, the decisive issue is instead that the operator's working set exceeds the memory available to the query.
This action targets query history long runtimes, where single-run timing cannot distinguish workload trends, parameter changes and warehouse effects. The corrective response here is “Review query memory demand and benchmark suitable warehouse sizing or aggregation rewrites”; this alternative does not provide that response.
This action targets query profile pruning evidence, where the team has not identified whether ineffective partition pruning drives the scan. The stem reports a complex aggregation produces unusually high local disk spill and runs slower on a small warehouse than in a representative benchmark; this control addresses a different failure condition.