Query Queuing & Concurrency Management
A Virtual Warehouse in Snowflake has a finite amount of CPU and Memory. When too many users submit queries to the same warehouse at the exact same time, the warehouse reaches its concurrency limit.
When this happens, Snowflake does not crash or reject the queries. Instead, it places the excess queries into a Queue, where they wait for resources to free up.
Topics Covered
- Understanding Queuing and
MAX_CONCURRENCY_LEVEL - Identifying Queuing in
QUERY_HISTORY - Scale-Up vs Scale-Out (Multi-Cluster Warehouses)
- Workload Isolation Strategies
Understanding Queuing
By default, Snowflake determines the maximum number of concurrent queries a warehouse can handle based on the warehouse size and the complexity of the queries (typically defaulting to 8). This is governed by the MAX_CONCURRENCY_LEVEL parameter.
If 10 business analysts in Mumbai submit heavy dashboards to a single X-Small warehouse simultaneously, 8 queries might execute, while 2 sit in the queue.
The Timeout Parameter
If a query sits in the queue for too long, the user experiences terrible performance. You can control the maximum allowed wait time using a parameter.
-- Tell the warehouse to cancel any query that sits in the queue longer than 5 minutes (300 seconds)
ALTER WAREHOUSE COMPUTE_WH SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 300;
Identifying Queuing in QUERY_HISTORY
As an administrator, your job is to identify when queuing is happening so you can take action. You use the ACCOUNT_USAGE.QUERY_HISTORY view for this.
(To ensure you have the ACCOUNTADMIN role to run this query, verify your current role using SELECT CURRENT_ROLE();)
USE ROLE ACCOUNTADMIN;
-- Find queries that spent more than 10 seconds waiting in the queue over the last 7 days
SELECT
QUERY_ID,
USER_NAME,
WAREHOUSE_NAME,
QUEUED_OVERLOAD_TIME / 1000 AS QUEUED_SECONDS,
EXECUTION_TIME / 1000 AS EXEC_SECONDS,
START_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD(day, -7, CURRENT_DATE())
AND QUEUED_OVERLOAD_TIME > 10000 -- more than 10 seconds
ORDER BY QUEUED_OVERLOAD_TIME DESC;
How to Fix Queuing: Scale-Up vs Scale-Out
If you identify severe queuing, you have two options:
1. Scale-Up (Bigger Warehouse)
Scaling up means changing the warehouse from Small to Medium to Large.
- When to do it: When individual queries are taking too long to execute because they are scanning massive amounts of data (e.g., Terabytes).
- Why it doesn't always fix queuing: A bigger warehouse processes large datasets faster, but it doesn't significantly increase the number of concurrent connections allowed.
2. Scale-Out (Multi-Cluster Warehouses)
Scaling out means keeping the warehouse size the same (e.g., Small), but allowing Snowflake to spin up duplicate Small warehouses automatically when concurrency is exhausted.
- When to do it: When hundreds of users are submitting small, fast queries simultaneously (e.g., Monday morning dashboard refreshes).
- How it works: If Cluster 1 hits its limit, Snowflake instantly provisions Cluster 2 to handle the overflow, and later auto-suspends it when the queue drops.
Hands-On: Configuring a Multi-Cluster Warehouse
Let's configure a BI warehouse for our analysts across India.
USE ROLE SYSADMIN;
CREATE OR REPLACE WAREHOUSE BI_ANALYTICS_WH
WITH WAREHOUSE_SIZE = 'SMALL'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 3 -- Scale out up to 3 clusters during heavy concurrency
SCALING_POLICY = 'STANDARD' -- Instantly starts the next cluster when a query queues
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE;
Scaling Policies:
STANDARD: Start the next cluster immediately to prevent queuing. (Optimizes for user experience).ECONOMY: Only start the next cluster if Snowflake estimates there is enough queued work to keep it busy for at least 6 minutes. (Optimizes for cost).
Workload Isolation Strategies
The best way to prevent queuing is to avoid putting all your workloads on the same warehouse. This is called Workload Isolation.
Never run massive ETL data loads on the same warehouse that the CEO uses to load their morning dashboard. The ETL job will consume all the concurrency, putting the CEO's dashboard query into the queue!
Example Architecture
DATA_ENGINEERING_WH(Large, Single-Cluster): Used bydbtand Airflow to run massive overnight transformations.BI_REPORTING_WH(Small, Multi-Cluster 1-5): Used by Tableau/PowerBI for hundreds of business users.AD_HOC_WH(Medium, Auto-Suspend 5 min): Used by Data Scientists running exploratory queries.
Summary
- Queuing happens when a warehouse hits its
MAX_CONCURRENCY_LEVEL. - Use
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORYto trackQUEUED_OVERLOAD_TIME. - Scale-Up to fix slow, heavy queries.
- Scale-Out (Multi-Cluster) to fix high-concurrency queuing.
- Always practice Workload Isolation by creating dedicated warehouses for distinct teams or tasks.