ADA-C01 Question 217
Single answerGiven a scenario, manage warehouse usage in sessions, and size the warehouse accordinglyA BI team uses a shared warehouse named BI_WH for interactive dashboards. During business hours, users frequently report that some dashboard queries wait in the queue for 20-40 seconds before starting, even though each query typically finishes in less than 5 seconds once it begins running. The current warehouse is configured as X-SMALL, AUTO_SUSPEND = 60, AUTO_RESUME = TRUE, and MAX_CLUSTER_COUNT = 1. Query history shows many short, concurrent queries from different sessions, with low data spill and no unusual query complexity. The administrator wants to reduce user wait time while controlling cost and without changing the SQL generated by the BI tool. Which action is the BEST choice?
- A
Increase the warehouse size from X-SMALL to LARGE so each query uses more compute, even though the main issue is queuing from concurrency.
- B
Convert BI_WH to a multi-cluster warehouse and allow additional clusters so concurrent sessions can run short queries in parallel.
- C
Disable AUTO_SUSPEND so the warehouse stays running all day and eliminates queuing caused by concurrent sessions.
- D
Increase STATEMENT_TIMEOUT_IN_SECONDS for BI users so queued statements are less likely to fail before they start.
Show answer and explanation
Correct answer: B
Explanation
The scenario points to a concurrency problem, not a query performance problem. Key clues are: many short queries from different sessions, low spill, no unusual complexity, and long queue times before execution begins. In Snowflake, warehouse sizing should match the workload pattern. If queries are slow because each one needs more compute, scaling up the warehouse size can help. If queries are fast once running but users are waiting because too many queries arrive at the same time, a multi-cluster warehouse is typically the better choice because it adds clusters to serve concurrent workloads. AUTO_SUSPEND and AUTO_RESUME settings affect startup behavior and cost management, but they do not resolve active concurrency bottlenecks. This aligns with Snowflake best practices for warehouse tuning: scale up for larger or more resource-intensive queries, and scale out with multi-cluster warehouses for high-concurrency workloads such as BI and dashboarding.
- A. Incorrect.
Incorrect. Increasing warehouse size can help when individual queries are compute-bound, such as when queries need more CPU, memory, or local cache to execute faster. In this scenario, the evidence says each query runs quickly once it starts, with the main problem being queue time caused by many concurrent short queries from different sessions. Simply scaling up a single-cluster warehouse may not address concurrency as effectively as adding clusters.
- B. Correct.
Correct. A multi-cluster warehouse is designed to handle higher concurrency by automatically starting additional clusters when needed. Because the workload consists of many short, concurrent dashboard queries from multiple sessions and the queries themselves are not complex, the bottleneck is concurrent execution slots rather than per-query compute power. Allowing additional clusters is the most targeted way to reduce queueing while still letting Snowflake scale back when demand falls.
- C. Incorrect.
Incorrect. Disabling AUTO_SUSPEND may reduce cold-start delays after inactivity, but it does not solve the main issue described here: queries are queuing during active periods because too many sessions are competing for a single cluster. Keeping the warehouse running continuously would likely increase cost without materially improving concurrency-driven wait times.
- D. Incorrect.
Incorrect. Increasing statement timeout only allows statements to wait longer before failing; it does not reduce queue duration or improve throughput. This option addresses a symptom rather than the underlying cause. Users would still experience poor performance, just with a longer tolerance for waiting.