ADA-C01 Question 293
Select 34.1 Monitor and analyze Snowflake performance.A Snowflake administrator is investigating complaints that a dashboard query has become much slower during business hours. The query usually completes in under 10 seconds on a MEDIUM warehouse, but several executions took more than 2 minutes between 9:00 AM and 11:00 AM. The SQL text has not changed. The administrator wants to determine whether the slowdown is caused by warehouse contention, query queuing, or an issue in the query execution itself. Which TWO actions provide the most direct evidence for this analysis?
- A
Review the query in Snowsight Query History and inspect execution details such as queued time, compilation time, and execution time for the slow runs.
- B
Check WAREHOUSE_LOAD_HISTORY for the affected warehouse during the time window to identify queued overload and concurrency patterns.
- C
Run SHOW PARAMETERS IN ACCOUNT and compare the output to previous days to determine whether account-level settings caused the slowdown.
- D
Query TABLE_STORAGE_METRICS for the tables used by the dashboard to determine whether increased table size directly indicates warehouse contention.
- E
Open the Query Profile for a slow execution to identify whether time was spent in specific operators, such as scans, joins, or remote disk I/O.
Show answer and explanation
Correct answers: A, B, E
Explanation
To analyze Snowflake query performance effectively, administrators typically correlate evidence from multiple sources. Query History provides per-query timing details such as queued, compilation, and execution times. Query Profile provides operator-level visibility into how the query executed and where time was spent. WAREHOUSE_LOAD_HISTORY helps determine whether the warehouse was under concurrency pressure or experiencing queued overload during the slowdown window. Together, these tools support a practical root-cause analysis: if queued time and warehouse load increase during business hours, contention is likely; if queued time is low but Query Profile shows expensive operators or remote I/O, the issue is more likely inside execution. This aligns with Snowflake best practices for monitoring and troubleshooting performance using Query History, Query Profile, and Account Usage warehouse monitoring views.
- A. Correct.
Correct. Query History in Snowsight exposes key timing components for each execution, including queued time, compilation time, and execution time. This is one of the most direct ways to distinguish whether the delay came from waiting for warehouse resources versus the query actually spending more time running. If queued time is high during business hours, that strongly suggests contention or concurrency pressure on the warehouse.
- B. Correct.
Correct. WAREHOUSE_LOAD_HISTORY is specifically designed to help analyze warehouse activity over time, including load and queuing behavior. Reviewing it for the affected warehouse and timeframe helps determine whether the warehouse experienced overload, concurrency bottlenecks, or queuing that aligns with the dashboard slowdown. This is a core administrative performance-monitoring technique.
- C. Incorrect.
Incorrect. SHOW PARAMETERS IN ACCOUNT lists configuration parameters, but it is not a direct performance-monitoring tool for isolating a time-bound slowdown of a specific query. While configuration changes can matter in some cases, this action does not directly reveal whether the issue was warehouse queuing, contention, or operator-level execution behavior during the affected period.
- D. Incorrect.
Incorrect. TABLE_STORAGE_METRICS is useful for understanding table storage characteristics, retention, and bytes stored, but it does not directly diagnose warehouse contention or query queuing. A larger table may contribute to longer scans, but storage metrics alone do not provide evidence about why this query became slow only during a specific business-hours window.
- E. Correct.
Correct. The Query Profile is essential for understanding where execution time was spent inside the query plan. It can reveal whether the slowdown was due to scans, joins, spills, remote disk I/O, data redistribution, or other operator-level factors. Combined with Query History and warehouse load analysis, it helps separate warehouse contention from inefficient execution behavior.