Resolving memory spillage

This topic discusses how a warehouse owner or administrator can resolve memory spillage in order to improve the performance of a query.

Performance degrades drastically when a warehouse runs out of memory while executing a query because memory bytes must “spill” onto local disk storage. If the query requires even more memory, it spills onto remote cloud-provider storage, which results in even worse performance.

Note

You must have access to the shared SNOWFLAKE database to execute the diagnostic queries provided in this topic. By default, only the ACCOUNTADMIN role has the privileges needed to execute the queries.

Finding queries that spill to storage

This query identifies the top 10 worst offending queries in terms of bytes spilled to local and remote storage.

SELECT query_id, SUBSTR(query_text, 1, 50) partial_query_text, user_name, warehouse_name,
  bytes_spilled_to_local_storage, bytes_spilled_to_remote_storage
FROM  snowflake.account_usage.query_history
WHERE (bytes_spilled_to_local_storage > 0
  OR  bytes_spilled_to_remote_storage > 0 )
  AND start_time::date > dateadd('days', -45, current_date)
ORDER BY bytes_spilled_to_remote_storage, bytes_spilled_to_local_storage DESC
LIMIT 10;
Copy

Options for resolving memory spillage

When memory spillage is the issue, you can convert your existing warehouse to a Snowpark-optimized warehouse, which provides 16x more memory per node and 10x the local cache compared to a standard warehouse. Though a larger warehouse also has more memory available, a query might not require its expanded compute resources.

If you want to try resolving the spillage of a query without adjusting the warehouse that runs it, use the Query Profile to identify which operation nodes are causing the spillage.

Cost considerations

When a query requires additional memory without additional compute, it is more cost effective to switch to a Snowpark-optimized warehouse rather than increasing the size of the warehouse.

The following is a side-by-side comparison of the credit consumption for a standard warehouse and a Snowpark-optimized warehouse:

Type of Virtual Warehouse

X-Small

Small

Medium

Large

X-Large

2X-Large

3X-Large

4X-Large

5X-Large

6X-Large

Standard

1

2

4

8

16

32

64

128

256

512

Snowpark-optimized

n/a

n/a

6

12

24

48

96

192

384

768

How to convert to a Snowpark-optimized warehouse

Note that X-Small and Small warehouses cannot be converted to a Snowpark-optimized warehouse unless you increase the size first.

To convert an existing standard warehouse to a Snowpark-optimized warehouse:

Snowsight:

  1. Sign in to Snowsight.

  2. Navigate to Admin » Warehouses.

  3. Find the warehouse, and select … » Suspend. A warehouse must be suspended before changing its type.

  4. Select … » Edit.

  5. From the Warehouse Type drop-down, select Snowpark-optimized.

  6. Select Save Warehouse.

SQL:

Use the ALTER WAREHOUSE command to change the warehouse type. The warehouse must be suspended before changing its type. For example:

ALTER WAREHOUSE SUSPEND;
ALTER WAREHOUSE my_analytics_wh SET WAREHOUSE_TYPE='snowpark-optimized';
Copy
Language: English