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
In a nutshell
  • 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.

Warehouserunningqueries arriveBusy creditsuseful workno queriesIdle creditsbilled until auto suspendsuspendNo credits

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.

SizeCredits per hour
X-Small1
Small2
Medium4
Large8
X-Large16
2X-Large32
3X-Large64
4X-Large128

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

SettingMeaning
AUTO_SUSPENDSeconds of inactivity before the warehouse suspends; 0 or NULL means never
AUTO_RESUMEThe warehouse starts automatically when a query arrives
ALTER WAREHOUSE ... SUSPEND or RESUMEManual 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.
WorkloadSuggested behavior
Sporadic ad hoc queries, ETL batchesShort auto suspend, such as 60 seconds
Dashboards hit all day with repeated queriesLonger auto suspend, or a dedicated warehouse that stays warm
Nightly jobResume, 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.

Watch out

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

ViewUse
ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORYHourly credits per warehouse, with compute and cloud services parts
ACCOUNT_USAGE.METERING_HISTORYHourly credits for all services
ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORYLoad: running, queued and blocked queries
ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORYCompute 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_SUSPEND on warehouses with sparse traffic.
  • Consolidate small, lightly used warehouses so queries share one warehouse.
  • Check WAREHOUSE_LOAD_HISTORY for 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

StrategyWhen it helps
Right sizeQueries spill or queue (size up); a warehouse is mostly idle with low load (size down)
Separate warehouses by workloadIsolates ETL from BI so each can be sized and suspended on its own
Multi cluster with MIN_CLUSTER_COUNT of 1Pay for extra clusters only when concurrency needs them
Economy scaling policyKeeps clusters fully loaded before starting new ones, saving credits at the cost of some queuing
Resource monitorsNotify or suspend at a credit quota
Query acceleration serviceLets 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_COMPUTE minus CREDITS_ATTRIBUTED_COMPUTE_QUERIES.
  • AUTO_SUSPEND of 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_QUERIES from CREDITS_USED_COMPUTE in WAREHOUSE_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.