28 February 2019 · n-pacha ·       Add to Favorites   Report

How to increase concurrency of query execution in Snowflake

Understanding how the threads are handled internally inside your virtual warehouses will equip you to handle production loads effectively. WIth unlimited computing power, you get the luxury to support high concurrency.

Each node in a snowflake cluster has 8 threads by default (you can increase or decrease this value using MAX_CONCURRENCY_LEVEL parameter). Each increase in warehouse size also increases the concurrency level. So, an S (Small) would have 16, an M (Medium) would have 32, and so on. So, to increase concurrency, you simply increase the warehouse size.

When the level is reached, the operation performed depends on whether the warehouse is single-cluster or multi-cluster:

  • Single-cluster: Statements are queued until already-allocated resources are freed or additional resources are provisioned, which can be accomplished by increasing the size of the warehouse.
  • Multi-cluster (in Auto-scale mode): Additional clusters are started

Also, Note that this parameter does not limit the number of statements that can be executed concurrently by a warehouse cluster. Instead, it serves as an upper-boundary to protect against over-allocation of resources. 

And behold, snowflake doesn't recommend tuning these parameters, so make sure to try it in your sandbox environment first before you go live!


posted on 28 February 2019

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too