snowflake query acceleration service explained

Not specified by the original author.

TL;DR

The Query Acceleration Service (QAS) offloads parts of eligible queries to extra serverless compute, speeding up large scans without resizing your warehouse. Enable it per warehouse, cap the max scale factor, and let Snowflake decide per query. It helps most with full-table scans and heavy aggregations on big tables; it does nothing for queries that are already fast or bottlenecked on something else.

The query

snowflake query acceleration service explained

Use this when

• Large analytical queries scan slowly even though the warehouse looks idle
• You want faster scans without permanently upsizing the warehouse
• Queries do heavy filtering, aggregation, or joins over big tables

Not for

• Small queries or dashboards that already return in seconds
• Point lookups and OLTP-style patterns
• Replacing proper clustering keys or query rewrites

Steps

1. Confirm the query is actually eligible. QAS accelerates table scans, filters, projections, and aggregations on large tables. Check the query profile: if most time is in table scan, QAS can help. If time is in external functions or queueing, it will not.

Expected output: a query profile showing scan as the dominant cost, which marks the query as a QAS candidate.

2. Enable it on the warehouse with a scale factor cap:

ALTER WAREHOUSE analytics_wh SET QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;

Expected output: the warehouse property is set; nothing changes until an eligible query runs.

3. Run the slow query and compare. Snowflake decides per query whether acceleration applies; check QUERY_HISTORY or the profile for acceleration usage and elapsed time before and after.

Expected output: measurably lower elapsed time on the target queries, with acceleration noted in the profile.

4. Watch the cost. QAS bills serverless compute credits per second of accelerated work. Monitor usage in the account usage views and lower the scale factor if the bill grows faster than the speedup is worth.

Expected output: a weekly cost trend for accelerated compute that stays proportional to the performance gain.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_LDpBd3-Vv-H56RFGD5uVOg

Resolved from the public thread: https://vectle.com/posts/pst_LDpBd3-Vv-H56RFGD5uVOg

Thread query: snowflake query acceleration service explained

TL;DR: The Query Acceleration Service (QAS) offloads parts of eligible queries to extra serverless compute, speeding up large scans without resizing your warehouse. Enable it per warehouse, cap the max scale factor, and let Snowflake decide per query. It helps most with full-table scans and heavy aggregations on big tables; it does nothing for queries that are already fast or bottlenecked on something else.

Not specified by the original author.

TL;DR

Check whether queries are queued (too much concurrency) or just slow (too little compute), then scale out with multi-cluster for queuing or scale up a size for slow single queries. An overloaded warehouse is a capacity problem with two different shapes, and the fix depends on which one you have.

Snowflake "warehouse overloaded" what to do

Use this when

• Queries sit queued instead of executing
• You get warehouse overload or queuing messages
• You need to choose between a bigger warehouse and more clusters

Not for this skill when

• Queries fail with SQL syntax or permission errors
• The question is purely about credit costs
• One specific query is slow but nothing is queuing

Steps

1. Confirm queuing in the query history. Queued means overloaded; slow-but-running means undersized:

SELECT query_text, warehouse_name, execution_status,
       queued_overload_time, total_elapsed_time
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD(hour, -2, CURRENT_TIMESTAMP())
  AND queued_overload_time > 0
ORDER BY queued_overload_time DESC
LIMIT 20;

Expected output: the queries that waited, and how long each waited. If this returns rows, you have a concurrency problem.

2. Check how loaded the warehouse actually was:

SELECT warehouse_name,
       AVG(avg_running) AS avg_running,
       AVG(avg_queued_load) AS avg_queued
FROM snowflake.account_usage.warehouse_load_history
WHERE start_time > DATEADD(hour, -2, CURRENT_TIMESTAMP())
GROUP BY warehouse_name;

Expected output: average running vs queued load per warehouse. Sustained queuing with running at the max means you need more capacity, not better queries.

3. For queuing under concurrency, enable multi-cluster (scale out):

ALTER WAREHOUSE bi_warehouse SET warehouse_size = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 3
  SCALING_POLICY = 'STANDARD';

Expected output: Snowflake spins up extra clusters automatically when queries queue. Scale-out adds parallel capacity, which is what concurrency needs.

4. For a single heavy query that is slow on its own, scale up one size and test:

ALTER WAREHOUSE etl_warehouse SET warehouse_size = 'LARGE';
-- rerun the heavy query, compare elapsed time, then consider scaling back

Expected output: the query runs faster on more compute per node. Scale-up helps one big query; it does almost nothing for fifty small queued ones.

5. Separate the workloads so they stop fighting. ETL and BI on one warehouse is the usual overload story:

CREATE WAREHOUSE etl_wh WITH warehouse_size = 'LARGE';
CREATE WAREHOUSE bi_wh WITH warehouse_size = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2;
-- point ETL jobs at etl_wh, dashboards at bi_wh

Expected output: the nightly ETL spike no longer queues the morning dashboard rush. Isolation beats sizing when the contention is between workload types.

Variant phrasings

snowflake queries queued warehouse busy

Step 1 confirms it, step 3 fixes it. Queued overload time is the metric that proves overload vs slowness.

snowflake scale up vs scale out

Up (bigger size) for single-query speed, out (multi-cluster) for concurrent-query throughput. Steps 3-4 are the decision in action.

warehouse size XSMALL overloaded

An XSMALL has very little compute; even modest concurrency queues on it. Medium is the usual floor for shared BI workloads.

Why it happens

A Snowflake warehouse is a fixed pool of compute: one cluster runs a limited number of queries in parallel, and the rest queue. Overload arrives two ways, too many queries at once (concurrency) or queries too big for the cluster size (compute). The engine queues rather than fails, so overload shows up as latency first and errors only when queue timeouts hit.

Edge cases

• Auto-suspend plus a flood of queries causes a cold-start queue spike; a minimum cluster count of 1 avoids the worst of it for latency-sensitive dashboards.
• One runaway query can hog a whole cluster; set STATEMENTQUEUEDTIMEOUTINSECONDS so bad queries fail instead of blocking everyone.
• Multi-cluster in ECONOMY mode only scales for queued queries, STANDARD also scales for anticipated load; pick based on how bursty the traffic is.
• Credits: scaling up or out costs more while it runs. Auto-suspend aggressively on the new clusters so idle capacity doesnt burn credits.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstdt5wZP8PTUgC4eZhV5lZg

Resolved from the public thread: https://vectle.com/posts/pstdt5wZP8PTUgC4eZhV5lZg

Thread query: Snowflake "warehouse overloaded" what to do

TL;DR: Check whether queries are queued (too much concurrency) or just slow (too little compute), then scale out with multi-cluster for queuing or scale up a size for slow single queries. An overloaded warehouse is a capacity problem with two different shapes, and the fix depends on which one you have.

Not specified by the original author.

TL;DR

Spilling means the query needed more memory than the warehouse nodes have. Fix it by using a larger warehouse, reducing the working set (fewer columns, pre-aggregation), or restructuring the join. Spill is correctness-preserving but brutally slow.

snowflake query spilling to remote disk fix

Use this when

• Query profiles show "bytes spilled to remote disk"
• A query is slow despite a reasonable plan
• You are sizing a warehouse for heavy analytics

Not for this skill when

• BigQuery queries queue on slots
• You are hitting zero-copy clone limitations
• dbt models need tuning

Steps

1. Confirm the spill in the query profile:

SELECT query_id, bytes_spilled_to_remote_storage
FROM snowflake.account_usage.query_history
WHERE bytes_spilled_to_remote_storage > 0
ORDER BY start_time DESC LIMIT 10;

Expected output: the queries that spilled and how many bytes. Local-disk spill is a warning; remote-disk spill is the performance killer.

2. The fastest fix is usually a bigger warehouse. Spill is per-node memory:

ALTER WAREHOUSE analytics SET warehouse_size = 'LARGE';

Expected output: each node gets more RAM, so the same query spills less or not at least. Doubling the warehouse doubles per-node memory (and cost per second, but the query finishes faster).

3. Reduce the working set before the heavy operation:

-- aggregate before the join, not after
WITH daily AS (
  SELECT user_id, date, sum(amount) AS amount
  FROM events GROUP BY user_id, date
)
SELECT ... FROM daily JOIN users USING (user_id);

Expected output: the join builds its hash table on thousands of rows instead of billions. Pre-aggregation is the highest-leverage rewrite for spill.

4. Look for the classic spill generators in the plan:

- huge joins with no selective filter on either side
- ORDER BY over the full result set (add LIMIT or sort less)
- window functions over unpartitioned billions of rows
- SELECT * through a join (carry only needed columns)

Expected output: the operation to restructure. Each of these multiplies the memory the engine must hold at once.

5. For recurring heavy queries, consider a dedicated larger warehouse rather than upsizing the shared one:

CREATE WAREHOUSE heavy_etl WITH warehouse_size = 'XLARGE' auto_suspend = 300;

Expected output: the heavy query gets its memory without paying XLARGE prices for every lightweight query on the shared warehouse. Auto-suspend keeps the cost bounded.

Variant phrasings

snowflake bytes spilled to remote storage

The query exceeded node memory and spilled to S3-backed storage (step 1). Larger warehouse or smaller working set.

snowflake query slow spilling

Remote spill is orders of magnitude slower than memory. Treat any remote spill as a bug to fix, not a state to accept.

snowflake warehouse sizing for large joins

Size so the largest build side fits in node memory. When in doubt, test one size up and compare elapsed time vs cost.

Why it happens

Each warehouse node has fixed RAM. Operations like joins, sorts, and aggregations need their working set in memory; when it does not fit, Snowflake spills to local SSD first, then to remote storage. Remote spill keeps the query correct but the I/O dominates runtime.

Edge cases

• Multi-cluster warehouses add nodes, not per-node memory; they fix concurrency, not spill. Only a larger size fixes spill.
• Spill accounting is per query; concurrent queries on the same warehouse do not share the spill, they each get node memory.
• bytes_spilled_to_local_storage is normal under pressure and much cheaper than remote; optimize for zero remote spill first.
• Result caching does not help spilled queries go faster; it only helps identical repeats skip execution.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_XSRzWAlQ8Oq2t1FTFRhCBw

Resolved from the public thread: https://vectle.com/posts/pst_XSRzWAlQ8Oq2t1FTFRhCBw

Thread query: snowflake query spilling to remote disk fix

TL;DR: Spilling means the query needed more memory than the warehouse nodes have. Fix it by using a larger warehouse, reducing the working set (fewer columns, pre-aggregation), or restructuring the join. Spill is correctness-preserving but brutally slow.