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
In a nutshell
  • WAREHOUSE_LOAD_HISTORY columns: 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.

Complaintslow or costlymeasureMonitoring dataload, queue, spill, scandiagnoseBottleneckone of fourfixTargeted change

How it works

Warehouse monitoring sources

SourceWhat it shows
Snowsight Admin, WarehousesCredit use, load charts and queued queries per warehouse
WAREHOUSE_LOAD_HISTORYFive minute averages: AVG_RUNNING, AVG_QUEUED_LOAD, AVG_QUEUED_PROVISIONING, AVG_BLOCKED
WAREHOUSE_METERING_HISTORYCredits used per warehouse per hour
QUERY_HISTORYPer query time, queue, scan, spill and cache columns
SHOW WAREHOUSESCurrent 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

BottleneckSignFirst fix
QueuingQUEUED_OVERLOAD_TIME high, AVG_QUEUED_LOAD above zeroMulti-cluster, separate warehouse, or lower concurrency per workload
SpillingBytes spilled to remote storageLarger or Snowpark-optimized warehouse, rewrite
Excess scanningPartitions scanned near total, high bytes scannedClustering, search optimization, better predicates
Compute shortage on one queryLong run time, no spill, no queueScale 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.

AreaTypical findingFix
Warehouse layoutOne warehouse serves ETL, BI and data scienceSeparate warehouses per workload
SizingToo large for small queries, or too small for heavy onesRight size per workload
ScaleConcurrency spikes at fixed timesMulti-cluster auto-scale
Table layoutLarge tables filtered on unclustered columnsClustering key, search optimization
Query shapeSELECT *, functions on filtered columns, exploding joins, UNION instead of UNION ALLRewrite
Repeated workSame heavy aggregates run all dayMaterialized view, dynamic table, result cache
Many small loadsThousands of tiny files and frequent small DMLBatch loads, right file size
Data typesDates stored as strings, JSON everywhereTyped columns
Exam tip

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

Queued load highand warehouse always busymeansUnder provisionedadd clusters or sizeQueued load zeroand running lowmeansOver provisionedreduce size or suspend soonerSpill highqueue lowmeansMemory boundscale up
  • 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_HISTORY columns: 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_INFORMATION to confirm clustering explains poor pruning.
  • Test cheap fixes first and measure again after every change.

Check yourself

  • A warehouse shows AVG_QUEUED_LOAD above 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_depth from SYSTEM$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.