Performance Monitoring and Tuning · Topic 14 of 16
Warehouse Costs, Suspend and Resume, and Idle Time
Control virtual warehouse cost: when to suspend or resume, how to calculate credits by size, cost saving strategies and how to measure and cut idle time.
- 5 min
- Billing is per second with a 60 second minimum on each resume.
- Credits per hour double with each size, from 1 for X-Small.
- Suspending clears the local disk cache; the result cache is not tied to the running warehouse.
What it is
A virtual warehouse consumes credits for as long as it runs, whether or not queries are executing. Warehouse cost therefore has two parts: credits that did work, and credits spent waiting for work, called idle time.
The administrator's job is to size the warehouse to the work, stop it when no one is using it, and prove the result with usage views.
How it works
Credits by size
Credits per hour double with each size. Billing is per second, with a 60 second minimum every time the warehouse starts or resumes.
| Size | Credits per hour |
|---|---|
| X-Small | 1 |
| Small | 2 |
| Medium | 4 |
| Large | 8 |
| X-Large | 16 |
| 2X-Large | 32 |
| 3X-Large | 64 |
| 4X-Large | 128 |
A multi cluster warehouse bills each running cluster. A Large warehouse with 3 clusters running for one hour uses 24 credits.
Example: an X-Small warehouse that runs 30 minutes uses 0.5 credit. If it is resumed and suspended five times in an hour, each start is billed for at least 60 seconds even when the query takes 5 seconds.
Suspend and resume
| Setting | Meaning |
|---|---|
AUTO_SUSPEND | Seconds of inactivity before the warehouse suspends; 0 or NULL means never |
AUTO_RESUME | The warehouse starts automatically when a query arrives |
ALTER WAREHOUSE ... SUSPEND or RESUME | Manual control |
ALTER WAREHOUSE bi_wh SET AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
Suspend decisions balance two costs.
- Suspending early saves idle credits but empties the warehouse local disk cache. The next queries re read data from remote storage and run slower.
- Staying up longer keeps the cache warm for repeat queries but pays for idle time.
| Workload | Suggested behavior |
|---|---|
| Sporadic ad hoc queries, ETL batches | Short auto suspend, such as 60 seconds |
| Dashboards hit all day with repeated queries | Longer auto suspend, or a dedicated warehouse that stays warm |
| Nightly job | Resume, run, suspend; consider a schedule or the job suspending it |
A very short interval with frequent resumes can pay the 60 second minimum over and over, so a few seconds of auto suspend is rarely a saving on a warehouse that is used often.
AUTO_SUSPEND = 0 or NULL means the warehouse never suspends. A forgotten 2X-Large that never suspends burns 32 credits every hour.
Calculating warehouse usage and credits
| View | Use |
|---|---|
ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY | Hourly credits per warehouse, with compute and cloud services parts |
ACCOUNT_USAGE.METERING_HISTORY | Hourly credits for all services |
ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY | Load: running, queued and blocked queries |
ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY | Compute credits attributed to each query |
SELECT warehouse_name, SUM(credits_used) AS credits
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY credits DESC;
CREDITS_USED includes cloud services. Use CREDITS_USED_COMPUTE for warehouse compute only.
Idle time
Idle cost is the compute the warehouse was billed for that no query used. The view WAREHOUSE_METERING_HISTORY has CREDITS_USED_COMPUTE and CREDITS_ATTRIBUTED_COMPUTE_QUERIES. Their difference is idle time.
SELECT warehouse_name,
SUM(credits_used_compute) AS billed,
SUM(credits_attributed_compute_queries) AS used_by_queries,
SUM(credits_used_compute) - SUM(credits_attributed_compute_queries) AS idle
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD('day', -10, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY idle DESC;
QUERY_ATTRIBUTION_HISTORY gives per query cost and does not include idle time, which is why the two numbers differ.
How to minimize idle time:
- Lower
AUTO_SUSPENDon warehouses with sparse traffic. - Consolidate small, lightly used warehouses so queries share one warehouse.
- Check
WAREHOUSE_LOAD_HISTORYfor warehouses with low average running queries and shrink them. - Make sure scripts and BI tools do not run tiny queries on a timer that keep the warehouse awake.
Cost saving strategies
| Strategy | When it helps |
|---|---|
| Right size | Queries spill or queue (size up); a warehouse is mostly idle with low load (size down) |
| Separate warehouses by workload | Isolates ETL from BI so each can be sized and suspended on its own |
Multi cluster with MIN_CLUSTER_COUNT of 1 | Pay for extra clusters only when concurrency needs them |
| Economy scaling policy | Keeps clusters fully loaded before starting new ones, saving credits at the cost of some queuing |
| Resource monitors | Notify or suspend at a credit quota |
| Query acceleration service | Lets a smaller warehouse handle occasional large scans |
A larger warehouse is not always more expensive per query: if doubling the size halves the runtime, the credits stay the same and the user waits less.
Common mistakes
Setting auto suspend to never "for speed". The cache benefit rarely outweighs paying for 24 hours.
Setting auto suspend to a few seconds on a busy dashboard. The cache is lost and every resume costs at least 60 seconds.
Comparing total credits to query attributed credits and calling the gap an error. The gap is idle time.
Sizing up to fix queuing on a single warehouse. Queuing from concurrency needs more clusters, not necessarily more size.
What to remember for the exam
- Billing is per second with a 60 second minimum on each resume.
- Credits per hour double with each size, from 1 for X-Small.
- Suspending clears the local disk cache; the result cache is not tied to the running warehouse.
- Idle time =
CREDITS_USED_COMPUTEminusCREDITS_ATTRIBUTED_COMPUTE_QUERIES. AUTO_SUSPENDof 0 or NULL means never suspend.
Check yourself
Warehouse X-Large runs 1 hour with 2 clusters. How many credits?
32 credits: 16 per cluster per hour times 2.
How do you measure idle time for a warehouse?
Subtract
CREDITS_ATTRIBUTED_COMPUTE_QUERIESfromCREDITS_USED_COMPUTEinWAREHOUSE_METERING_HISTORY.What is the cost of suspending a warehouse too eagerly?
The local disk cache is lost, so later queries are slower, and each resume bills at least 60 seconds.
Topic 14 of 16 in Performance Monitoring and Tuning
Test yourself on Performance Monitoring and Tuning
The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.