DEA-C02 Performance Optimization Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✓

  Implement a separate warehouse for small, frequent queries and another for large, long-running queries.

  Why this is correct

  Workload separation isolates small queries from large ones, preventing large queries from monopolizing resources and causing queueing for small queries. By dedicating a warehouse to small, frequent queries, they can run with minimal queueing, while the other warehouse handles heavy workloads. This is a best practice for concurrency management and can be cost-effective if each warehouse is sized appropriately and auto-suspend is enabled.

• ✗

  Increase the warehouse size from Medium to Large.

  Why it's wrong here

  Scaling up a warehouse increases compute power for individual queries, but it does not increase concurrency. A larger warehouse still has a fixed number of concurrent query slots, and adding more resources to a single cluster can actually increase queueing if many small queries are competing. The bottleneck is concurrency, not per-query performance, so scaling up is not the right solution and may increase cost without reducing queueing.

• ✗

  Set the STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter to a low value.

  Why it's wrong here

  This parameter controls how long a statement waits in the queue before timing out. Setting it low would cause queries to fail rather than reduce queueing. It does not increase concurrency or throughput; it simply aborts waiting queries. While it can free up queue slots, it does so by failing queries, which is not a performance optimization and could lead to application errors. It does not address the root cause of high concurrency.

• ✓

  Enable the multi-cluster warehouse feature and set the minimum and maximum clusters to 2.

  Why this is correct

  Multi-cluster warehouses automatically add compute clusters when queries are queued, allowing concurrent queries to run in parallel. Setting min and max to 2 ensures at least one cluster is always available and up to two clusters can handle bursts. This directly reduces queueing for high-concurrency workloads, and because clusters only spin up when needed, cost is controlled. It is a recommended practice for unpredictable or spiky concurrency.

• ✗

  Enable the 'USE_CACHED_RESULT' parameter and increase the result cache size.

  Why it's wrong here

  Result caching can speed up repeated identical queries, but it does not reduce queueing for new or unique queries. The scenario describes many small, frequent queries that are likely different, so caching would have limited impact. Also, there is no parameter to increase result cache size; it is managed by Snowflake. This option does not address the concurrency bottleneck and is not a recommended practice for reducing queueing.