When Faster Is Cheaper: A Snowflake Spilling Story

One of the ways we can see that a query might be handling too much data for a particular warehouse is by looking at spilling. This article doesn’t apply to adaptive warehouses as I haven’t had the chance to experiment with them just yet.

Behind the Scenes

Cloud services are not magic. A Snowflake Gen1 or Gen2 warehouse is composed of multiple nodes. Each node has computing resources – memory, cpu, local disk cache. While the node is working with data, it must be available locally to the node. This isn’t a problem, no matter the data size, for a simple select * – in many of those cases, it is just about building the result set and providing it to the client. The data that becomes more problematic is data that must be worked with together or as a set. We are normally talking about data processing for aggregations, joins, sorts, window functions, and other operations.

If the data is too big or the node is too busy with multiple queries all using that memory, then data “spills” to local disk cache on the node. If the data is too big for local disk cache, or the node’s local disk cache is filled by other queries, then the data spills to remote cache – back to cloud object storage.

Remote spilling is particularly problematic for performance because data must be written to and read from the slower object storage. Local spilling is more expected at small scale, but can serve as a warning flag that the data set, the query logic, and the warehouse size or concurrency are somehow mismatched.

The knowledge base article on “Performance impact from local and remote disk spilling” lays out the basics quite simply.

Resolving Spilling

My knee-jerk response to seeing remote spilling is to increase the warehouse size and observe the impact that has on performance.

Another reasonable route to take is to use QUERY_HISTORY to understand what other queries were running at the time and whether any of those queries might have been competing for resources. Running the same query on an isolated warehouse of the same size is a good experiment that may indicate whether resource contention may be contributing to the issue.

Yet another approach to spilling is to look at which operator is seeing the spilling, using the query profile, and see if I can reduce the data going into that operator. I look for opportunities to push filtering predicates down below the spilling operator, reducing the number of rows. This usually involves either using a CTE to push the optimizer to maybe do the filtering first, or involves applying the same predicate in more than one place within the query. I look for opportunities to make a narrower aggregation key using tricks like ANY_VALUE(). I look for a selective column list and whether a large variant that may not be needed is being handled by the spilling operator – reducing the number or size of columns. These actions often involve query rewrite.

I don’t see local spilling as a red flag by itself. I may try a larger warehouse size if I only see local spilling to see whether reducing spilling helps performance, but it’s not usually the first thing I look at or try.

Quick Experiment

I tend to be on a “show and tell” kick these days, so I thought I would use an example scenario using SNOWFLAKE_SAMPLE_DATA. In this scenario, I create a warehouse at each size and test a simple CTAS (Create Table AS) with an ORDER BY to force remote spilling on an XS, and then track the performance as I move up to each warehouse size.

Here is the code I ran. Note that I walked away while these were running, because the XS warehouse took over an hour.

CREATE OR REPLACE WAREHOUSE ecrooks_gen2_xs_wh 
    WITH WAREHOUSE_SIZE='XSMALL' 
    AUTO_SUSPEND=60
    GENERATION='2';
USE WAREHOUSE ecrooks_gen2_xs_wh;
CREATE OR REPLACE TABLE LINEITEM_ORDERED_XS
AS
  SELECT * 
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM 
  ORDER BY l_shipdate;
CREATE OR REPLACE WAREHOUSE ecrooks_gen2_s_wh 
    WITH WAREHOUSE_SIZE='SMALL' 
    AUTO_SUSPEND=60
    GENERATION='2';
USE WAREHOUSE ecrooks_gen2_s_wh;
CREATE OR REPLACE TABLE LINEITEM_ORDERED_S
AS
  SELECT * 
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM 
  ORDER BY l_shipdate;
CREATE OR REPLACE WAREHOUSE ecrooks_gen2_m_wh 
    WITH WAREHOUSE_SIZE='MEDIUM' 
    AUTO_SUSPEND=60
    GENERATION='2';
USE WAREHOUSE ecrooks_gen2_m_wh;
CREATE OR REPLACE TABLE LINEITEM_ORDERED_M
AS
  SELECT * 
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM 
  ORDER BY l_shipdate;
CREATE OR REPLACE WAREHOUSE ecrooks_gen2_l_wh 
    WITH WAREHOUSE_SIZE='LARGE' 
    AUTO_SUSPEND=60
    GENERATION='2';
USE WAREHOUSE ecrooks_gen2_l_wh;
CREATE OR REPLACE TABLE LINEITEM_ORDERED_L
AS
  SELECT * 
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM 
  ORDER BY l_shipdate;
CREATE OR REPLACE WAREHOUSE ecrooks_gen2_xl_wh 
    WITH WAREHOUSE_SIZE='XLARGE' 
    AUTO_SUSPEND=60
    GENERATION='2';
USE WAREHOUSE ecrooks_gen2_xl_wh;
CREATE OR REPLACE TABLE LINEITEM_ORDERED_XL
AS
  SELECT * 
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM 
  ORDER BY l_shipdate;
CREATE OR REPLACE WAREHOUSE ecrooks_gen2_xxl_wh 
    WITH WAREHOUSE_SIZE='XXLARGE' 
    AUTO_SUSPEND=60
    GENERATION='2';
USE WAREHOUSE ecrooks_gen2_xxl_wh;
CREATE OR REPLACE TABLE LINEITEM_ORDERED_XXL
AS
  SELECT * 
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM 
  ORDER BY l_shipdate;

Results

There was nothing surprising in the results here. On pure cost, the largest warehouse we tried (2XL) is by far the fastest and the cheapest. The times looked like this:

The Deviation column represents how much better the reduction in execution time is than simply halving the time as the warehouse size goes up. Each expectation is based on 1/2 the actual time of a warehouse that is one size smaller. A negative number for Deviation indicates the query is both faster and cheaper, while a positive number would indicate that the query is faster but not cheaper. Here’s a visual representation of this data:

Both Medium and 2XL sizes look like excellent choices for this particular query.

This data makes a bit more sense if we look at what happens with spilling at each warehouse size:

We can see here that local spilling is significantly reduced from small to medium and again from XL to 2XL. Which warehouse size to pick really depends on what makes sense in context of what your target time is, what other warehouse sizes you work with regularly, and how critical cost is.

Summary

Like many other areas of Snowflake performance, experimentation should be a part of your daily practice if the best performance, the best cost, or the best price/performance are your goals. As in many other areas of Snowflake performance, faster can actually also be cheaper.