Diagnosing Snowflake Concurrency: Queued Queries, Multi-Cluster Scaling Policies, and the Query Acceleration Service

"Snowflake is slow this morning" almost never means the engine got slower. Nine times out of ten the query itself ran in four seconds and waited forty. Concurrency problems look like performance problems, they are fixed with completely different levers, and throwing a bigger warehouse at them usually costs money without moving the number.

This tutorial shows how to tell the two apart, and then how to fix the concurrency half properly: scaling out versus scaling up, multi-cluster scaling policies, the Query Acceleration Service, and workload isolation.

First: is it queuing, or is it the query?

Every Snowflake query records where its wall-clock time went. Start here rather than in the Query Profile.

SELECT
  warehouse_name,
  DATE_TRUNC('hour', start_time)                       AS hr,
  COUNT(*)                                             AS queries,
  ROUND(AVG(execution_time)/1000, 1)                   AS avg_exec_s,
  ROUND(AVG(queued_overload_time)/1000, 1)             AS avg_queue_overload_s,
  ROUND(AVG(queued_provisioning_time)/1000, 1)         AS avg_queue_provision_s,
  ROUND(AVG(compilation_time)/1000, 1)                 AS avg_compile_s,
  SUM(IFF(queued_overload_time > 0, 1, 0))             AS queries_that_queued
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
  AND warehouse_name IS NOT NULL
GROUP BY 1, 2
HAVING SUM(queued_overload_time) > 0
ORDER BY avg_queue_overload_s DESC
LIMIT 50;

Read the columns literally:

• queued_overload_time — the warehouse was busy; your query waited for a slot. This is a concurrency problem.
• queued_provisioning_time — Snowflake was still starting compute (cold warehouse, or a new cluster spinning up). This is a warm-up problem.
• compilation_time — planning, not waiting. Huge values usually mean enormous views, wide SELECT * over hundreds of columns, or thousands of micro-partitions being pruned at plan time.
• execution_time — the only number that a bigger warehouse or better clustering will reduce.

If avg_queue_overload_s dwarfs avg_exec_s, stop tuning SQL. You have a slot problem.

Why queries queue at all

A warehouse cluster admits a limited amount of concurrent work, governed by MAX_CONCURRENCY_LEVEL (default 8) and by how much memory and local SSD each running query claims. Heavy queries consume more than one "slot" worth of resources, so a warehouse advertising eight concurrent statements may admit far fewer large ones, and spill-heavy queries crowd out everything else.

Two instincts follow, and only one of them is usually right.

Symptom	Right lever
Individual queries are slow, profile shows spilling to remote storage	Scale up (bigger warehouse size)
Individual queries are fast, many of them, queued_overload_time high	Scale out (multi-cluster)
One giant scan occasionally blocks a shared warehouse	Isolate it onto its own warehouse, or enable QAS
Latency spikes only on the first query after idle	Warm-up: raise AUTO_SUSPEND, or pre-warm

Scaling up an ETL warehouse that is queuing because forty analysts hit it at 9am buys you faster individual queries and the same queue.

Scaling out: multi-cluster warehouses done right

CREATE OR REPLACE WAREHOUSE bi_wh
  WAREHOUSE_SIZE      = 'MEDIUM'
  MIN_CLUSTER_COUNT   = 1
  MAX_CLUSTER_COUNT   = 5
  SCALING_POLICY      = 'STANDARD'
  AUTO_SUSPEND        = 120
  AUTO_RESUME         = TRUE
  INITIALLY_SUSPENDED = TRUE
  STATEMENT_TIMEOUT_IN_SECONDS        = 1800
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 300;

The two scaling policies behave very differently, and the choice is a cost/latency decision:

• STANDARD — starts an additional cluster as soon as a query queues (or is predicted to queue). Favours latency. Clusters shut down after roughly two-to-three consecutive checks showing the load could be redistributed. Use this for BI, dashboards, and anything a human is watching.
• ECONOMY — only starts a cluster when there is enough backlog to keep it busy for about six minutes. Favours credits over latency. Use this for queue-tolerant batch work and internal tools.

A few rules that save real money:

1. MIN_CLUSTER_COUNT = MAX_CLUSTER_COUNT is not "auto-scaling" — that is maximized mode, and you pay for every cluster the whole time the warehouse runs. Use it deliberately (a known trading-hours peak), never by accident.
2. Multi-cluster multiplies credits per hour by the number of running clusters. A MEDIUM at 5 clusters costs the same per hour as a 4X-LARGE. Set MAX_CLUSTER_COUNT to a number you are willing to pay for, and back it with a resource monitor.
3. Set STATEMENT_QUEUED_TIMEOUT_IN_SECONDS. Without it, a runaway backlog silently turns into ten-minute dashboard loads. With it, the query fails fast and someone finds out.

Check whether scaling out is actually happening:

SELECT
  warehouse_name,
  DATE_TRUNC('hour', start_time) AS hr,
  AVG(avg_running)               AS avg_running_queries,
  AVG(avg_queued_load)           AS avg_queued_load,
  MAX(avg_blocked)               AS max_blocked
FROM snowflake.account_usage.warehouse_load_history
WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY avg_queued_load DESC
LIMIT 50;

avg_queued_load persistently above zero on a warehouse already at MAX_CLUSTER_COUNT means raise the ceiling or split the workload. avg_blocked is different — that is transactional lock contention (concurrent UPDATE/MERGE on the same table), which no amount of compute fixes; fix it in the pipeline design instead.

The Query Acceleration Service: for the outliers, not the crowd

QAS lends serverless compute to individual queries whose plans contain large, parallelizable scans and filtering. It helps the one enormous query that would otherwise monopolize a cluster — which is very often the thing causing everyone else to queue.

ALTER WAREHOUSE bi_wh SET
  ENABLE_QUERY_ACCELERATION   = TRUE
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;   -- 0 = no explicit limit

Before switching it on, ask Snowflake which queries would actually benefit:

SELECT query_id, warehouse_name, eligible_query_acceleration_time,
       upper_limit_scale_factor
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY eligible_query_acceleration_time DESC
LIMIT 20;

If that view is nearly empty, QAS will do nothing for you except add a serverless line item. It does not help short lookup queries, heavily-cached dashboards, or DML-bound pipelines. The scale factor is a multiplier on the warehouse's own compute, and it is billed separately as serverless credits — start at 4 or 8 rather than unlimited.

Workload isolation: the fix that usually wins

Most queue problems in real accounts come from mixing incompatible workloads on one warehouse. The durable fix is boring:

-- Small, latency-sensitive, spiky: scale out
CREATE WAREHOUSE bi_wh      WAREHOUSE_SIZE='MEDIUM'  MIN_CLUSTER_COUNT=1 MAX_CLUSTER_COUNT=5 SCALING_POLICY='STANDARD';
-- Big, batch, queue-tolerant: scale up, single cluster
CREATE WAREHOUSE etl_wh     WAREHOUSE_SIZE='LARGE'   MAX_CLUSTER_COUNT=1 AUTO_SUSPEND=60;
-- Unpredictable human SQL: small, cheap, hard-capped
CREATE WAREHOUSE adhoc_wh   WAREHOUSE_SIZE='SMALL'   MIN_CLUSTER_COUNT=1 MAX_CLUSTER_COUNT=3 SCALING_POLICY='ECONOMY'
  STATEMENT_TIMEOUT_IN_SECONDS=900;

Then attach the warehouse to the role, not to the person, so cost attribution and access control stay aligned with your RBAC model. Add a resource monitor per warehouse with a notify-then-suspend trigger, and let AUTO_SUSPEND do its job: 60 seconds for batch warehouses, 120–300 for interactive ones where losing the local SSD cache between queries hurts more than the idle credits.

A repeatable weekly check

1. Rank warehouses by total queued_overload_time over seven days.
2. For the top offenders, compare avg_exec_s with avg_queue_overload_s — queue-dominated warehouses get more clusters or a workload split; execution-dominated ones get sizing, clustering, or SQL work.
3. Check warehouse_load_history for warehouses pinned at MAX_CLUSTER_COUNT.
4. Check QUERY_ACCELERATION_ELIGIBLE for scan-heavy outliers worth isolating or accelerating.
5. Confirm no warehouse is accidentally running in maximized mode.

That loop takes fifteen minutes and routinely removes more perceived slowness than a month of query rewriting.

---

If your Snowflake account is queuing at peak and nobody is sure whether to scale up, scale out, or split the workload, our consultants do this diagnosis as a fixed-scope engagement and hand back the warehouse topology, scaling policies, and resource monitors as code. Get in touch with your workload mix and peak-hour symptoms.