page106
88
Spills to Disk and Out of Memory
We will consider spill to disk and out of memory (OOM) within the same section; they
are related because spills can cause OOM events.
Every warehouse has a finite amount of memory allocated; you may recall from
Chapter 2 that I stated an extra small (XSmall) warehouse has eight CPUs and associated
cache, about 16 to 24GB RAM, local SSD storage, and remote attached storage. For
every size you increase your warehouse, the number of CPUs doubles and memory
increases too.
What Causes a Spill to Disk and OOM?
A spill to disk occurs when a query attempts to consume more memory than the
warehouse has available for allocation. In this scenario, intermediate results are first
spilled to local SSD storage and then to remote storage, before finally exceeding all
available memory and storage resulting in an OOM error. Snowflake will then attempt to
retry the query.
As Snowflake dynamically allocates CPU and memory, it is possible the warehouse
workload has reduced between query failure and query retry, rendering more resources
available for the retry attempt.
For your further investigation, an example scenario for spills to disk and OOM using
ORDER BY
and
LIMIT
/
OFFSET
is available at
https://community.snowflake.com/s/
article/Out-of-memory-error-caused-by-LIMIT-and-or-OFFSET-clause
.
You must understand the importance of sizing warehouses correctly according to
the expected workload. You also see how queries returning large volumes of data with
smaller warehouses may exceed the available memory and cause an OOM failure.
Identifying Spills to Disk
Spills to disk are readily identified from the statistics block of every query profile where
“spilling” information is presented when spills to disk occur.
In this section, you will identify both spills to disk and OOMs from all of those
queries that have been executed.
To illustrate a spill, let’s deliberately incorrectly size a warehouse and then execute a
poor-quality query. Note that you should never use
SELECT *
for your production code.
ChapTer 3
The Query profiler