How to know when Snowflake queries are spilling to storage

[bennieregenold7]

One of the biggest killers of performance in Snowflake is queries spilling to either local or remote storage. This happens when your query processes more data than your virtual warehouse can hold in memory, and is directly related to the size of your warehouse.

Find the problem

So, how do you know if you have spillage? The easiest way is to go into the query profile and look for the stats there. To do that, go to the History tab in the IDE and find the query that is performing poorly. Then, click on the Query ID field and click Profile. If your query has multiple steps you may need to click through Step 1, Step ..., until you find the one that's spilling.

In the example below you can see that 45.64 GB of data spilled to local storage. This spillage has a significant impact on performance because you incur the time it takes to move that data to storage, and then anytime that Snowflake needs to retrieve it, it is using less performant resources to pull it. For more specifics on this, checkout Snowflake documentation.

Fix the problem

There are a few things you can do to fix this problem:

1. Throw resources at it, and hope it goes away. This will cost you money, but can be a quick fix if you need a solution ASAP. The amount of memory that Snowflake has available for a given query is governed by warehouse size, so if you up the warehouse, you up your memory.
2. Process your data in smaller chunks. By limiting the amount of data that a query processes you can potentially prevent spilling anything to local/remote storage.
3. Watch out for big CTEs. If you're processing a ton of data in multiple CTEs in the same query there's a good chance you'll hit this problem. Since CTEs process their results in memory, it hogs that resource for the query. Try converting your largest CTEs in to views and see if that solves the problem.

[sfc-gh-ejohnson]

Thank you for this discussion.
Regarding #1, when you have spillage, increasing warehouse size almost always helps. However, it will not always cost you more money. In fact, it will save you money to increase your warehouse to the next T-shirt size (2x), if you get more than 2x performance increase. I have seen this many times.

Regarding #3, CTEs are perfectly fine to achieve optimal performance as long as you design them correctly. CTEs should select as little as needed, filter as early as possible and have that help filter larger datasets later. To get the best performance, CTEs should be written as an inverted funnel.