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!

nVector

posted on 28 Feb 19