This topic provides general guidelines and best practices for using virtual warehouses in Snowflake to process queries. It does not provide specific or absolute numbers, values, or recommendations because every query scenario is different and is affected by numerous factors, including number of concurrent users/queries, number of tables being queried, and data size and composition, as well as your specific requirements for warehouse availability, latency, and cost.
It also does not cover warehouse considerations for data loading, which are covered in another topic (see the sidebar).
The keys to using warehouses effectively and efficiently are:
- Experiment with different types of queries and different warehouse sizes to determine the combinations that best meet your specific query needs and workload.
- Don’t focus on warehouse size. Snowflake utilizes per-second billing, so you can run larger warehouses (Large, XL, 2XL, etc.) and simply suspend them when not in use.
These guidelines and best practices apply to both single-cluster warehouses, which are standard for all accounts, and multi-cluster warehouses, which are available in Snowflake Enterprise Edition (and higher).
In this Topic:
- How are Credits Charged for Warehouses?
- How Does Query Composition Impact Warehouse Processing?
- How Does Warehouse Caching Impact Queries?
- Creating a Warehouse
- Scaling Up vs Scaling Out
How are Credits Charged for Warehouses?¶
Credit charges are calculated based on the number of servers per cluster (determined by warehouse size), the number of clusters (if using multi-cluster warehouses), and the length of time each server in each cluster runs.
|XS warehouse:||Utilizes 1 server per cluster and bills 1 credit per full, continuous hour that each cluster runs; each successive size doubles the number of servers per cluster.|
|4XL warehouse:||Utilizes 128 servers per cluster and bills 128 credits per full, continuous hour that each cluster runs.|
Note the following:
Each server in a warehouse cluster has:
- An internal timer that tracks when the server was started; this internal timer is used to calculate the individual credit billing charges for the server at per-second intervals.
- A position in the warehouse that is maintained, even when the warehouse is suspended or resized. This position impacts how servers are added and removed because servers are always removed in reverse order of when they were added (aka LIFO, “Last In, First Out”).
When a server is provisioned:
The minimum billing charge for provisioning a server is 1 minute (i.e. 60 seconds).
There is no benefit to stopping a warehouse before the first 60-second period is over because the credits have already been billed for that period.
After the first 60 seconds, all subsequent billing for a running server is per-second (until the server shuts down). In other words:
- If a server runs for 30 to 60 seconds, it is billed for 60 seconds.
- If a server runs for 61 seconds, it is billed for only 61 seconds.
- If a server runs for 61 seconds, shuts down, and then restarts and runs for 20 seconds more, it is billed for 121 seconds (60 + 1 + 60).
Resizing a warehouse provisions additional servers for each cluster in the warehouse:
- This results in a corresponding increase in the number of credits billed for the warehouse (while the additional servers are running).
- The internal timer for the additional servers starts when they are provisioned, i.e. credits for the additional servers are billed relative to the time when the warehouse was resized.
Credit usage is displayed in hour increments. With per-second billing, you will see fractional amounts for credit usage/billing.
How Does Query Composition Impact Warehouse Processing?¶
The number of servers required to process a query depends on the size and complexity of the query. For the most part, queries scale linearly with regards to warehouse size, particularly for larger, more complex queries:
- When considering factors that impact query processing, the overall data size of the tables being queried has more impact than the number of rows.
- Filtering in a query using predicates also has an impact on processing, as does the number of joins/tables in the query.
To achieve the best results, try to execute relatively homogeneous queries (size, complexity, data sets, etc.) on the same warehouse; executing queries of widely-varying size and/or complexity on the same warehouse makes it more difficult to analyze warehouse load, which can make it more difficult to select the best size to match the size, composition, and number of queries in your workload.
How Does Warehouse Caching Impact Queries?¶
Each warehouse, when running, maintains a cache of table data accessed as queries are processed by the warehouse. This enables improved performance for subsequent queries if they are able to read from the cache instead of from the table(s) in the query. The size of the cache is determined by the number of servers in the warehouse. i.e. the larger the warehouse (and, therefore, the number of servers in the warehouse), the larger the cache.
This cache is dropped when the warehouse is suspended, which may result in slower initial performance for some queries after the warehouse is resumed. As the resumed warehouse runs and processes more queries, the cache is rebuilt, and queries that are able to take advantage of the cache will experience improved performance.
Keep this in mind when deciding whether to suspend a warehouse or leave it running. In other words, consider the trade-off between saving credits by suspending a warehouse versus maintaining the cache of data from previous queries to help with performance.
Creating a Warehouse¶
When creating a warehouse, the two most critical factors to consider, from a cost and performance perspective, are:
- Warehouse size (i.e. number of servers per cluster)
- Manual vs automated management (for starting/resuming and suspending warehouses)
The number of clusters in the warehouse is also important (if you are using Snowflake Enterprise Edition (or higher) and multi-cluster warehouses. For more details, see Scaling Up vs Scaling Out (in this topic).
Selecting an Initial Warehouse Size¶
With per-second credit billing, we recommend starting with larger warehouse sizes (Large, X-Large, 2X-Large, etc.) rather than smaller sizes (Medium, Small, etc.). You can always decrease the size of a warehouse at any time.
However, larger is not necessarily faster for smaller, more basic queries. In general, try to match the size of the warehouse to the expected size and complexity of the queries to be processed by the warehouse. Small/simple queries typically do not need an X-Large (or larger) warehouse because they won’t necessarily benefit from the additional resources, regardless of the number of queries being processed concurrently.
Experiment by running the same queries against warehouses of multiple sizes (e.g. X-Large, Large, Medium). The queries you use for these experiments should be of a size and complexity that you know will typically complete within 5 to 10 minutes (or less).
Automating Warehouse Suspension¶
Warehouses can be set to automatically suspend when there’s no activity after a specified period of time. Auto-suspend is enabled by specifying the time period of inactivity for the warehouse.
Note that the default for auto-suspend is different depending on how the warehouse is created:
Web Interface: The minimum supported value is 5 minutes and the default is 10 minutes. SQL: The minimum supported value is 60 seconds and there is no default, i.e. the warehouse does not suspend on inactivity.
We recommend setting auto-suspend according to your workload and your requirements for warehouse availability:
Consider disabling auto-suspend for a warehouse if:
- You have a steady workload for the warehouse.
- You require the warehouse to be available with no delay or lag time. Server provisioning is generally very fast (e.g. 1 or 2 seconds); however, depending on the size of the warehouse and the availability of servers to provision, it can take longer.
If you enable auto-suspend, we recommend setting it to a low value (e.g. 5 or 10 minutes or less) because Snowflake utilizes per-second billing. This will help keep your warehouses from running (and consuming credits) when not in use.
However, the value you set should match the gaps, if any, in your query workload. For example, if you have regular gaps of 2 or 3 minutes between incoming queries, it doesn’t make sense to set auto-suspend to 1 or 2 minutes because your warehouse will be in a continual state of suspending and resuming (if auto-resume is also enabled) and each time it resumes, you are billed for the minimum credit usage (i.e. 60 seconds).
Automating Warehouse Resumption¶
Warehouses can be set to automatically resume when new queries are submitted. Note that auto-resume is disabled by default, regardless of whether the warehouse is created in the web interface or through SQL.
We recommend enabling/disabling auto-resume depending on how much control you wish to exert over usage of a particular warehouse:
- If cost and access are not an issue, enable auto-resume to ensure that the warehouse starts whenever needed. Keep in mind that there might be a short delay in the resumption of the warehouse due to server provisioning.
- If you wish to control costs and/or user access, leave auto-resume disabled and instead manually resume the warehouse when needed.
Scaling Up vs Scaling Out¶
Snowflake supports two ways to scale warehouses:
- Scale up by resizing a warehouse.
- Scale out by adding clusters to a warehouse (requires Snowflake Enterprise Edition).
Warehouse Resizing Improves Performance¶
Resizing a warehouse generally improves query performance, particularly for larger, more complex queries. It can also help reduce the queuing that occurs if a warehouse does not have enough servers to process all the queries that are submitted concurrently. Note that warehouse resizing is not intended for handling concurrency issues; instead, use additional warehouses to handle the workload or use a multi-cluster warehouse (if this feature is available for your account).
Snowflake supports resizing a warehouse at any time, even while running. If a query is running slowly and you have additional queries of similar size and complexity that you want to run on the same warehouse, you might choose to resize the warehouse while it is running; however, note the following:
- As stated earlier about warehouse size, larger is not necessarily faster; for smaller, basic queries that are already executing quickly, you may not see any significant improvement after resizing.
- Resizing a running warehouse does not impact queries that are already being processed by the warehouse; the additional servers are only used for queued and new queries.
Decreasing the size of a running warehouse removes servers from the warehouse. When the servers are removed, the cache associated with the servers is dropped, which can impact performance in the same way that suspending the warehouse can impact performance after it is resumed.
Keep this in mind when choosing whether to decrease the size of a running warehouse or keep it at the current size. In other words, there is a trade-off with regards to saving credits versus maintaining the server cache.
Multi-cluster Warehouses Improve Concurrency¶
Multi-cluster warehouses are designed specifically for handling queuing and performance issues related to large numbers of concurrent users and/or queries. In addition, multi-cluster warehouses can help automate this process if your number of users/queries tend to fluctuate.
When deciding whether to use multi-cluster warehouses and the number of clusters to use per warehouse, consider the following:
- If you are using Snowflake Enterprise Edition (or a higher edition), all your warehouses should be configured as multi-cluster warehouses.
- Unless you have a specific requirement for running in Maximized mode, multi-cluster warehouses should be configured to run in Auto-scale mode, which enables Snowflake to automatically start and stop clusters as needed.
- When choosing the minimum and maximum number of clusters for a multi-cluster warehouse:
Minimum: Keep the default value of 1; this ensures that additional clusters are only started as needed. However, if high-availability of the warehouse is a concern, set the value higher than 1. This helps ensure warehouse availability and continuity in the unlikely event that a cluster fails. Maximum: Set this value as large as possible, while being mindful of the warehouse size and corresponding credit costs. For example, an XL warehouse (16 servers) with maximum clusters = 10 will consume 160 credits in an hour if all 10 clusters run continuously for the hour.