Optimizing warehouses for performance¶
In the Snowflake architecture, virtual warehouses provide the computing power that is required to execute queries. Fine-tuning the compute resources provided by a warehouse can improve the performance of a query or set of queries.
A warehouse owner or administrator can try the following warehouse-related strategies as they attempt to improve the performance of one or more queries. As they adjust a warehouse based on one of these strategies, they can test the change by re-running the query and checking its execution time.
Warehouse-related strategies are just one way to boost the performance of queries. For performance strategies involving how data is stored, refer to Optimizing storage for performance.
Strategy |
Description |
|---|---|
Minimizing queuing can improve performance because the time between submitting a query and getting its results is longer when the query must wait in a queue before starting. |
|
Adjusting the available memory of a warehouse can improve performance because a query runs substantially slower when a warehouse runs out of memory, which results in bytes “spilling” onto storage. |
|
The larger a warehouse, the more compute resources are available to execute a query or set of queries. |
|
The query acceleration service offloads portions of query processing to serverless compute resources, which speeds up the processing of a query while reducing its demand on the warehouse’s compute resources. |
|
Query performance improves if a query can read from the warehouse’s cache instead of from tables. |
|
Limiting the number of queries that are running concurrently in a warehouse can improve performance because there are fewer queries putting demands on the warehouse’s resources. |
Tip
Optimizing a warehouse for query performance is more straightforward when the warehouse runs similar workloads. For example, if a warehouse runs significantly different queries, the cost of a performance enhancement might be wasted on a query that does not benefit from the optimization.
For general guidelines about distributing workloads to your organization’s warehouses, see the Analyzing Your Workloads section of the Managing Snowflake’s Compute Resources (Snowflake blog).