ADA-C01 Question 292
Select 34.1 Monitor and analyze Snowflake performance.A Snowflake administrator is investigating a dashboard workload that became slower after a recent release. Business users report that the same BI queries now take 2-3 times longer during peak hours, even though warehouse size has not changed. The administrator needs to determine whether the slowdown is caused primarily by warehouse contention or by changes in query execution characteristics. Which TWO actions would provide the most direct evidence to make that determination?
- A
Review the Query History for the affected statements and compare queued overload time, execution time, and bytes scanned before and after the release.
- B
Use the warehouse load and concurrency metrics in Snowsight/ACCOUNT_USAGE to see whether the warehouse was saturated or queries were spending time waiting for compute resources.
- C
Check whether Time Travel retention was increased on the source tables, because higher retention directly increases SELECT query runtime for current data reads.
- D
Inspect the Query Profile for representative slow queries to identify plan changes such as additional scans, repartitioning, remote disk I/O, or join explosion.
- E
Increase the STATEMENT_TIMEOUT_IN_SECONDS parameter so long-running BI queries can complete without being cancelled during peak periods.
Show answer and explanation
Correct answers: A, B, D
Explanation
To determine whether slower performance is caused by warehouse contention or by changes in query execution, the administrator should examine both workload-level and query-level evidence. Query History provides timing breakdowns such as queued versus execution time, which helps identify whether statements are waiting on resources. Warehouse monitoring in Snowsight or via views in SNOWFLAKE.ACCOUNT_USAGE can show whether concurrency/load increased enough to saturate the warehouse. Query Profile then provides execution-plan evidence for SQL regressions, such as more data scanned, reduced micro-partition pruning, expensive repartitioning, join explosion, or spill to remote storage. These are standard Snowflake performance investigation practices documented across Snowflake guidance for Query History, Query Profile, and warehouse/workload monitoring. By combining queue metrics with execution-plan analysis, an administrator can separate compute contention from query design or data-layout regressions.
- A. Correct.
Correct. Query History is one of the most direct places to distinguish queueing from actual execution regressions. If queued overload/provisioning/repair time increased while execution characteristics stayed similar, that points to warehouse contention. If execution time and scan metrics changed materially with little queueing, the issue is more likely related to query plan changes, data volume, or pruning effectiveness.
- B. Correct.
Correct. Warehouse monitoring metrics help determine whether the virtual warehouse is under concurrency pressure or saturation during peak periods. Looking at load, queued queries, and related utilization indicators provides direct evidence that compute resources are the bottleneck rather than the SQL itself.
- C. Incorrect.
Incorrect. Time Travel retention affects data retention/storage behavior and some administrative capabilities, but it does not directly make normal SELECT queries against current table data slower in the way described. This is a common misconception when administrators are looking for recent configuration changes.
- D. Correct.
Correct. Query Profile is the best tool for analyzing what changed inside query execution. It can reveal increased scan volume, poor partition pruning, expensive joins, data redistribution, spilling/remote disk I/O, and other execution-level regressions introduced by schema, SQL, or data changes.
- E. Incorrect.
Incorrect. Raising STATEMENT_TIMEOUT_IN_SECONDS does not help determine the root cause of slower performance, nor does it improve execution speed. It only changes how long Snowflake allows a statement to run before timing out. This addresses symptom handling, not performance analysis.