Snowflake doesn’t have a lot of configuration parameters that you can tune for query performance, but there are still a number of aspects and techniques to get better performance. The goal of this series is to explore the basic aspects of improving performance on Snowflake.
What is a Virtual Warehouse?
When I moved from a more traditional DBMS to working with snowflake, one of the early struggles was to understand the use of the term warehouse. I was used to the term being used as a part of Data Warehouse. I thought of a data warehouse as a database on a server(virtual or physical) with dedicated storage and other hardware resoureces. The data in a traditional DBMS data warehouse is deeply linked to the hardware where the data resides. Another data warehouse mostly can’t come in and query the data in my data warehouse. If I want the data from two warehouses together, I have to copy or move them to the same location for querying.
A virtual warehouse in Snowflake is a very different thing. A virtual warehouse is essentially just the compute and memory that we use for a query – with the data being entirely detached. The same data is available to an almost unlimited number of virtual warehouses to work with at the same time or different times, without any data movement. This allows us to apply only a small amount of compute and memory for very small queries, and a huge amount of compute for very large queries. We can allocate and pay for that compute only when it is needed for queries. With warm pools of compute resoucres that Snowflake maintains, often a warehouse can go from “suspended” to returning results in a second or even less. We can ensure that queries of similar patterns run together to take advantage of shared data or resources, or we can apply totally different compute to isolate workloads that are very different.
Scaling Virtual Warehouses Up
A virtual warehouse consists of one or more VMs behind the scenes. Virtual warehouse sizes are specified in t-shirt sizes such as extra-small or medium. As of the time this blog entry is published, the largest size available on some cloud providers is 6xl. Generally speaking, the computing power doubles with each increase in t-shirt size. The 6xl virtual warehouse has about 512 times the computing power of the extra-small.
The type of VM and exact computing resources of that VM vary over time, between deployments, and with some specific scenarios. When working with large data sets and complex queries, the largest warehouse sizes may have thousands of cores and dozens of VMS. The doubling of resources from one level to the next is usually (but not always) done by doubling the number of VMs involved. There could be some inefficiences of that scaling in the communication between VMs and in the risk of data skew.
Generally speaking, we hope that for each step up the ladder on warehouse size, the time for a query would cut in half. There are plenty of real-world scenarios that can trip this up and make it untrue – in either direction. If, for example, we have so much data to work with that we are encountering remote spilling, and we can increase the warehouse size to a point where remote spilling no longer happens, we might see a very drastic improvement in performance – much greater than simply cutting the time in half.
To give a general idea of what this looks like, let’s take a fairly simple query that does need a bit of compute time against the SNOWFLAKE_SAMPLE_DATA and run it on different virtual warehouse sizes. Here’s the code I’ll use:
alter session set use_cached_result=false;
use warehouse ecrooks_xxl_wh;
alter session set query_tag='32_XXL';
select o_custkey
, sum(o_totalprice)
, count(*)
from snowflake_sample_data.tpch_sf1000.orders
group by o_custkey
order by o_custkey;
alter session unset query_tag;
use warehouse ecrooks_xl_wh;
alter session set query_tag='16_XL';
select o_custkey
, sum(o_totalprice)
, count(*)
from snowflake_sample_data.tpch_sf1000.orders
group by o_custkey
order by o_custkey;
alter session unset query_tag;
use warehouse ecrooks_l_wh;
alter session set query_tag='8_L';
select o_custkey
, sum(o_totalprice)
, count(*)
from snowflake_sample_data.tpch_sf1000.orders
group by o_custkey
order by o_custkey;
alter session unset query_tag;
use warehouse ecrooks_m_wh;
alter session set query_tag='4_M';
select o_custkey
, sum(o_totalprice)
, count(*)
from snowflake_sample_data.tpch_sf1000.orders
group by o_custkey
order by o_custkey;
alter session unset query_tag;
use warehouse ecrooks_s_wh;
alter session set query_tag='2_S';
select o_custkey
, sum(o_totalprice)
, count(*)
from snowflake_sample_data.tpch_sf1000.orders
group by o_custkey
order by o_custkey;
alter session unset query_tag;
use warehouse ecrooks_xs_wh;
alter session set query_tag='1_XS';
select o_custkey
, sum(o_totalprice)
, count(*)
from snowflake_sample_data.tpch_sf1000.orders
group by o_custkey
order by o_custkey;
alter session unset query_tag;
This is a fairly unscientific test, since I'm only running each query one time, and I'm not leveraging the warehouse data cache at all, but plotting the results shows us this:
We can see here that the jump from XS(79.2s) to S(60.6s) doesn't get us all that much of a performance improvement, but the jump to M(27.1s) is huge, and then to L(12.8s) is also very good. Less than half the time of the smaller size means we are actually saving money to run the query faster. The benefit for this query drops off for XL(9.9) and XXL(5.9s). The larger sizes here end up not having a performance benefit to match their cost.
This is not an unusual pattern. It is interesting to do this kind of cost/benefit analysis for important or problematic queries to understand what virtual warehouse size works best for them.
Choosing the t-shirt size (XS, M, etc) is referred to scaling the warehouse out. We're making the virtual warehouse larger. This gives us more compute resources to throw at one query.
Scaling Virtual Warehouses Out
If we instead want to consider running more queries at one time, we can also scale virtual warehouses out, at least on Enterprise Edition or higher, using the Multi-Cluster Warehouse (MCW) feature. With this feature, we specify a minimum cluster size (MIN_CLUSTER_COUNT) and a maximum cluster size (MAX_CLUSTER_COUNT). Then a scaling policy decides when to spin up and spin down additional virtual warehouse clusters based on the concurrency activity for the virtual warehouse. Let's be clear - this doesn't make a single complex query go faster if run in isolation. It does help prevent queuing time and time spent waiting for virtual warehouse resources being used by other queries from contributing to query time when multiple queries are running at the same time.
Until recently, Snowflake had a limit on the MAX_CLUSTER_COUNT, but just this week, that limit has been increased for some virtual warehouse sizes.
Additional Warehouse Considerations for Performance
There are a few other performance-related things to consider for virtual warehouses.
Concurrency
As mentioned in the section on scaling virtual warehouses out above, concurrency can impact the performance of a single query. The goal in Snowflake is to dedicate all of the resources in the virtual warehouse to each query. This often works out when running more than one query at a time in the same warehouse, because different resources are needed at different times, and many queries may be short-running or require relatively little to run. However, if there are many queries competing for the same resources, queries may run slower even before MCW can scale out.
There is a parameter that you can set for a warehouse that controls the soft upper bound for queries running at the same time. The parameter is called MAX_CONCURRENCY_LEVEL. By default, it is set to 8. However, queries can run into resource issues before 8 is reached. Sometimes, decreasing MAX_CONCURRENCY_LEVEL can help performance by allowing the virtual warehouse to be dedicated to fewer queries at one time.
High Memory
Snowpark-optimized warehouses have higher memory that standard warehouses. They're also more expensive. If you're using Snowpark, consider using snowpark-optimized warehouses. For workloads that are not Snowpark, it often makes sense to jump up a warehouse size instead of moving to snowpark-optimized. It is a rare edge case where the cost-benefit of using a snowpark-optimized warehouse is better with a workload that is not from Snowpark.
Query Acceleration Service
Query Acceleration Service (QAS) is something that is enabled at the virtual warehouse level. That looks something like this:
create or replace warehouse ecrooks_xxl_wh
warehouse_size='XXLARGE'
auto_suspend=60
ENABLE_QUERY_ACCELERATION = true
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 2;Query acceleration service is not a "go faster!" button. Query acceleration service is designed to help with the random queries that come in and scan many more micro-partitions than other queries running on a virtual warehouse tend to. Essentially what it does is spin up additional compute - up to QUERY_ACCELERATION_MAX_SCALE_FACTOR times the size of the warehouse. This doesn't mean we spin up this compute for every query. The compute is purpose-built for scanning more data faster. The ideal use case is when scanning a large number of micropartitions with a highly selective filter that is not accounted for by the way a table is clustered. It is designed (though not guaranteed) to be cost neutral. The time it spends with additional specialty nodes scanning should reduce the amount of time the warehouse spends running the query, or Snowflake will not choose to use QAS.
Summary
Understanding virtual warehouses and how to use them or tweak them to improve performance is one of the foundational areas when tuning performance in Snowflake. I am frequently talking to customers about how to leverage the details here to get better performance out of their existing query workloads.
