Resource size is the virtual warehouse setting that allocates compute resources for queries and other processing. Larger sizes provide roughly twice the resources of the preceding size and consume twice as many credits per hour, so sizing is a performance-and-cost decision. section 9.1.3 section 9.6
Choose a size by measuring a representative, sufficiently demanding query rather than a quick query whose differences are too small to observe. Create comparable warehouses, run the workload on each, and inspect execution time and query-profile statistics. section 9.2 section 9.2.2
Table 9.2 Comparison of query profile statistics after executing the query from listing 9.1 using differently sized virtual warehouses
|
Warehouse size
|
Credits per hour
|
Total execution time in seconds
|
Bytes spilled to local storage in MB
|
|---|---|---|---|
|
X-small
|
1
|
22
|
36.86
|
|
Small
|
2
|
10
|
38.06
|
|
Medium
|
4
|
6
|
0
|
|
Large
|
8
|
4
|
0
|
Compare credits per hour, execution time, and local spilling across X-small through Large warehouses. The observed pattern shows that faster execution does not automatically mean lower query credit consumption. section 9.2.2
Use the measurements to make a production choice:
- Balance hourly credit rate against elapsed time. A rough per-query estimate divides credits per hour by 3,600 and multiplies by execution seconds, while recognizing the initial 60-second billing period. section 9.2.2
- Treat concurrent use separately from a single-query test: queries can compete for the same warehouse resources, and active-warehouse credits apply regardless of query count. section 9.2.2
- Configure multicluster warehouses when additional compute capacity is needed; Snowflake can add and deactivate clusters according to configured parameters. section 9.1.3
- Consider Snowpark-optimized warehouses for workloads with large memory requirements, such as machine learning use cases. section 9.1.3
Diagnosing a warehouse that spills
A large aggregation-and-sort query on more than 700 million retail-sales records ran in 22 seconds and spilled 36.86 MB on X-small. Small reduced runtime to 10 seconds but still spilled; Medium and Large eliminated spilling. Before increasing size, filter the workload: adding a distance condition reduced the X-small run from 22 to 16 seconds because it processed less data. section 9.2 section 9.2.2 section 9.2.3
Spilling means the warehouse exhausted memory and wrote data to local disk, slowing execution; remote storage can slow it further. For grouping and sorting over large tables, first reduce processed data by rewriting or filtering the query. Increase resource size when that reduction is not possible. section 9.2.3 section 9.6
