Performance Optimization · Topic 7 of 9
Troubleshooting Performance and Warehouse Monitoring
Troubleshoot slow Snowflake workloads: read warehouse load, diagnose queuing, spilling and scans, and fix existing architectures with cheap steps first.
- 4 min
WAREHOUSE_LOAD_HISTORYcolumns:AVG_RUNNING,AVG_QUEUED_LOAD,AVG_QUEUED_PROVISIONING,AVG_BLOCKED.- Queuing means scale out; spilling means more memory; heavy scans mean layout fixes; single slow queries mean scale up or QAS.
- Separate warehouses by workload; right size; use auto suspend wisely.
What it is
Troubleshooting an existing architecture means finding which resource is the bottleneck and choosing the cheapest fix. Warehouse monitoring supplies the evidence: how busy each warehouse is, how long queries wait, how much they spill and how many credits they use.
The method is the same every time: measure, name the bottleneck, change one thing, measure again.
How it works
Warehouse monitoring sources
| Source | What it shows |
|---|---|
| Snowsight Admin, Warehouses | Credit use, load charts and queued queries per warehouse |
WAREHOUSE_LOAD_HISTORY | Five minute averages: AVG_RUNNING, AVG_QUEUED_LOAD, AVG_QUEUED_PROVISIONING, AVG_BLOCKED |
WAREHOUSE_METERING_HISTORY | Credits used per warehouse per hour |
QUERY_HISTORY | Per query time, queue, scan, spill and cache columns |
SHOW WAREHOUSES | Current state, size, cluster counts, queued and running counts |
SELECT warehouse_name,
AVG(avg_running) AS avg_running,
AVG(avg_queued_load) AS avg_queued
FROM snowflake.account_usage.warehouse_load_history
WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY avg_queued DESC;
A warehouse with high AVG_QUEUED_LOAD is overloaded. One with low AVG_RUNNING and a large size is probably oversized and wasting credits.
Four bottlenecks and their signs
| Bottleneck | Sign | First fix |
|---|---|---|
| Queuing | QUEUED_OVERLOAD_TIME high, AVG_QUEUED_LOAD above zero | Multi-cluster, separate warehouse, or lower concurrency per workload |
| Spilling | Bytes spilled to remote storage | Larger or Snowpark-optimized warehouse, rewrite |
| Excess scanning | Partitions scanned near total, high bytes scanned | Clustering, search optimization, better predicates |
| Compute shortage on one query | Long run time, no spill, no queue | Scale up, or query acceleration for scan heavy queries |
A fifth case is queuing on provisioning: QUEUED_PROVISIONING_TIME is high when a suspended warehouse starts. Keep it running during the business day, or accept the start time.
Troubleshooting existing architectures
Check each area and note the typical finding.
| Area | Typical finding | Fix |
|---|---|---|
| Warehouse layout | One warehouse serves ETL, BI and data science | Separate warehouses per workload |
| Sizing | Too large for small queries, or too small for heavy ones | Right size per workload |
| Scale | Concurrency spikes at fixed times | Multi-cluster auto-scale |
| Table layout | Large tables filtered on unclustered columns | Clustering key, search optimization |
| Query shape | SELECT *, functions on filtered columns, exploding joins, UNION instead of UNION ALL | Rewrite |
| Repeated work | Same heavy aggregates run all day | Materialized view, dynamic table, result cache |
| Many small loads | Thousands of tiny files and frequent small DML | Batch loads, right file size |
| Data types | Dates stored as strings, JSON everywhere | Typed columns |
Always test the cheap fixes (rewrite, layout, caching) before spending credits on larger warehouses or serverless features.
Using clustering information in troubleshooting
When a table scans too many partitions, compare the profile's partitions scanned and total with the table's clustering health.
SELECT SYSTEM$CLUSTERING_INFORMATION('sales', '(sale_date)');
A high average_depth or many overlapping partitions on the filtered column explains poor pruning. After adding or changing the key, run the function again, and rerun the slow query, to confirm that depth fell and scanned partitions dropped. If the table is small, or the cost of Automatic Clustering is higher than the savings, choose another fix.
Reading the load pattern
- Spiky load: use auto-scale multi-cluster, or schedule heavy jobs outside peak hours.
- Steady low load: reduce size and shorten auto suspend.
- Frequent heavy ETL beside BI: isolate ETL on its own warehouse so BI does not queue.
Cost side of monitoring
Compare credits with performance. A warehouse at double size that halves run time costs the same per query, so a faster result is free. A warehouse running 24 hours with few queries is waste, and the fix is auto suspend or consolidation. Use WAREHOUSE_METERING_HISTORY and QUERY_HISTORY together to find the expensive queries.
Common mistakes
Fixing without measuring. Guessing at size changes wastes credits.
Sharing one warehouse across unlike workloads. ETL and BI interfere with each other.
Looking only at average load. Peaks cause the complaints, so check by hour.
Adding features before rewriting. A better predicate is free.
What to remember for the exam
WAREHOUSE_LOAD_HISTORYcolumns:AVG_RUNNING,AVG_QUEUED_LOAD,AVG_QUEUED_PROVISIONING,AVG_BLOCKED.- Queuing means scale out; spilling means more memory; heavy scans mean layout fixes; single slow queries mean scale up or QAS.
- Separate warehouses by workload; right size; use auto suspend wisely.
- Use
SYSTEM$CLUSTERING_INFORMATIONto confirm clustering explains poor pruning. - Test cheap fixes first and measure again after every change.
Check yourself
A warehouse shows
AVG_QUEUED_LOADabove zero all afternoon. What is the architecture answer?Increase concurrency capacity with a multi-cluster warehouse or split workloads onto separate warehouses.
ETL and dashboards share one warehouse and dashboards stall during loads. What change?
Move ETL to its own warehouse, so each workload has independent capacity.
How do you confirm clustering as the cause of full scans?
Check partitions scanned versus total in the profile and
average_depthfromSYSTEM$CLUSTERING_INFORMATION.
Topic 7 of 9 in Performance Optimization
Test yourself on Performance Optimization
The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.