Snowflake Query Queuing: Analyzing Queued Overload

Queuing is an important topic to understand, whether you're optimizing costs or trying to make your queries run faster. Let's dive in!

What is Queuing in Snowflake?

When a query is submitted to run on a virtual warehouse, Snowflake will first check if there are enough computing resources available to run the query. If the warehouse is busy processing other queries and does not have enough capacity to run the new query, Snowflake will automatically put that query in a queue. As soon as the required capacity becomes available, Snowflake will immediately run that query.

All of this is managed behind the scenes for Snowflake users, and you do not have to do anything to enable this behavior.

What is Queued Overload?

When talking about query queuing, you will often hear queued overload. This is simply the name of the metric reported by Snowflake to measure queuing. queued overload time is the amount of time in milliseconds that a query sits in the queue. You can identify this time programmatically using the Snowflake query history view (more on this below).

Is Queuing Bad?

Whether queuing should be avoided is highly dependent on the application running on your Snowflake virtual warehouse. For example, if the warehouse is being used to serve interactive queries issued by humans, then you may want to avoid queuing because it means the users will have to wait longer than normal for their queries to run.

On the flip side, if your virtual warehouse is primarily used for automated processes like batch data transformations or for alerting, then queuing can be tolerable as long as the queries finish in a time that is acceptable to the business's needs. When a warehouse begins to queue queries, it means that the compute capacity is being fully utilized. From a cost optimization standpoint, this is a fantastic thing! Many Snowflake warehouses sit underutilized and result in wasted spend. Queuing is one way to know this is not happening!

How to avoid Queuing?

If you need to eliminate queuing, there are a few different ways you can avoid it:

1. Increase the warehouse size
2. Increase the maximum cluster count (if you are on an Enterprise or higher Snowflake Edition) so that Snowflake can spin up additional computing clusters when queuing starts to occur
3. Ensure the scaling policy is set to Standard. The Economy policy will wait for ~6 minutes of queuing before spinning up an additional cluster.

How to check for queuing?

There are three different ways to check if queries are being queued in your Snowflake virtual warehouse.

Method 1: Querying the query history

Snowflake provides a view called query_history for all its customers, which contains 1 row per query. You can query this view to see how long each query was queued for due to the warehouse being overloaded / fully saturated using the queued_overload_time column.

select 
    start_time,
    query_text,
    user_name,
    queued_overload_time,
    execution_time
from snowflake.account_usage.query_history
where
    start_time > current_date - 7
    and warehouse_name='YOUR_WAREHOUSE_NAME'

Method 2: Using the Queries tab in Snowflake

While your warehouse is actively being used, you can pull open the Query History tab in the Snowsight UI and look whether any queries are currently queued:

For historical queries, you can hover over the "Duration" column and you'll see the amount of time spent in queued overload.

Method 3: Using the warehouse load history graph

On the warehouses page in the Snowsight UI, you can look at the Warehouse Activity graph, which gives you a quick overview of how often there is queueing in the warehouse. This screenshot is from our the warehosue we run our dbt project on. There is consistent queueing in the warehouse, which is exactly what we want since it gives us confidence that the warehouse is being fully utilized. Even with this queuing, our dbt project runs finish well within the SLA so this queuing is not a concern for us.