Home / Data Platform / Warehouses

Data PlatformTroubleshootingIntermediate

Snowflake queries are slow: queuing, spilling and pruning

Use query history and the query profile to tell warehouse queuing from spilling and poor partition pruning, then fix it with multi-cluster scaling, sizing or clustering.

Deepak Bhardwaj2 min read

Applies toSnowflake (all editions; multi-cluster warehouses need Enterprise+)

1. Where does the time go?

SELECT query_id, warehouse_name, warehouse_size,
       total_elapsed_time/1000 AS total_s,
       queued_overload_time/1000 AS queued_s,
       compilation_time/1000 AS compile_s,
       execution_time/1000 AS exec_s,
       bytes_spilled_to_local_storage, bytes_spilled_to_remote_storage,
       partitions_scanned, partitions_total
FROM TABLE(information_schema.query_history(end_time_range_start => DATEADD('hour', -2, CURRENT_TIMESTAMP())))
WHERE execution_status = 'SUCCESS'
ORDER BY total_elapsed_time DESC
LIMIT 20;

(SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY has up to a year of history but can lag by up to about 45 minutes.)

2. Diagnose by symptom

Signal Problem Fix
High queued_overload_time Too many concurrent queries for the warehouse Multi-cluster warehouse (scale out), or separate warehouses per workload
Bytes spilled to local storage The query's working set is bigger than memory A bigger warehouse size (scale up), or reduce the data in joins and sorts
Bytes spilled to remote storage Severe spilling Definitely size up, or rewrite the query
partitions_scanned ≈ partitions_total on a big table No pruning Filter on columns the data is ordered or clustered by. Add a clustering key.
High compilation_time Very complex SQL or huge views Simplify, materialize intermediate results

3. Scale out vs scale up

-- Concurrency (queuing): add clusters
ALTER WAREHOUSE bi_wh SET MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 4 SCALING_POLICY = 'STANDARD';

-- Heavy single queries (spilling): bigger size
ALTER WAREHOUSE etl_wh SET WAREHOUSE_SIZE = 'LARGE';

Each size step doubles compute (and credits per hour). Check the query actually gets faster. Some queries don't parallelize well.

4. Query profile

Open the query in Query History → Query Profile and look at:

  • Most expensive nodes: often a Join exploding rows, because of a missing join condition or many-to-many keys.
  • TableScan → Partitions scanned vs total.
  • Remote disk IO percentage (spilling).

5. Pruning and clustering

SELECT SYSTEM$CLUSTERING_INFORMATION('analytics.fact_sales', '(sale_date)');
ALTER TABLE analytics.fact_sales CLUSTER BY (sale_date);

Add clustering only to large (multi-TB) tables whose filters consistently use the same columns. Automatic reclustering uses credits.

6. Caching

  • The result cache returns identical queries instantly, for 24 hours, if the data hasn't changed. Dashboards with CURRENT_TIMESTAMP() in the SQL bypass it.
  • The warehouse cache (local SSD) is lost when the warehouse suspends. Very aggressive auto-suspend on BI warehouses can make the first queries slow.

Tip

Set STATEMENT_TIMEOUT_IN_SECONDS and a resource monitor on every warehouse, so one runaway query can't run all night or burn the monthly credits.

Related fixes