Performance Optimization · Topic 3 of 9
Query Acceleration Service and Snowpark-Optimized Warehouses
Decide when to use query acceleration or a Snowpark-optimized warehouse: eligibility, scale factor, estimate functions, memory tiers and cost.
- 4 min
- QAS: Enterprise or higher, serverless,
ENABLE_QUERY_ACCELERATIONandQUERY_ACCELERATION_MAX_SCALE_FACTOR(default 8, 0 is unlimited). - Check eligibility with
SYSTEM$ESTIMATE_QUERY_ACCELERATIONor theQUERY_ACCELERATION_ELIGIBLEview. - QAS fits large selective scans among mixed workloads, not small scans, unselective filters or non deterministic queries.
What it is
Two features cover cases that a plain resize handles badly. The query acceleration service (QAS) lends extra serverless compute to a warehouse for parts of a large query, so one big scan does not force you to size the whole warehouse up. A Snowpark-optimized warehouse is a warehouse type with much more memory per node, built for memory hungry code such as ML training.
How it works
Query acceleration service
QAS offloads some of the scan and filter work of eligible queries to serverless compute. It suits workloads with a mix of short and long queries, where an occasional large scan would otherwise need a bigger warehouse for everyone.
ALTER WAREHOUSE reporting_wh SET
ENABLE_QUERY_ACCELERATION = TRUE
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;
| Setting | Meaning |
|---|---|
ENABLE_QUERY_ACCELERATION | Turns QAS on for the warehouse |
QUERY_ACCELERATION_MAX_SCALE_FACTOR | Upper limit on serverless compute, as a multiple of the warehouse size. Default 8; 0 means no limit |
QAS requires Enterprise Edition or higher. Its serverless compute is billed in addition to the warehouse, so the scale factor is a cost ceiling.
Good candidates are queries that scan a lot of data and then filter or aggregate down to a small result, ad hoc analytics with unpredictable size, and queries with large scans where the warehouse is otherwise sized fine.
Poor candidates are queries that are already fast, queries limited by a long sort or a join rather than a scan, queries without enough partitions or without selective enough filters, some queries with a LIMIT clause, and queries that use non deterministic functions such as SEQ or RANDOM.
Finding eligible queries
SYSTEM$ESTIMATE_QUERY_ACCELERATION('<query_id>')returns a JSON estimate of whether the query would be accelerated and how much faster at different scale factors.- The
QUERY_ACCELERATION_ELIGIBLEview in ACCOUNT_USAGE lists recent queries that could have benefited, with an estimated time and the scale factor needed. - The
QUERY_ACCELERATION_BYTES_SCANNEDcolumn in query history shows how much the acceleration service scanned for queries that used it.
Choose QAS when the problem is a few unpredictable heavy scans on a shared warehouse. Choose a larger warehouse when most queries are heavy, or a multi-cluster warehouse when the problem is concurrency.
Snowpark-optimized warehouses
A Snowpark-optimized warehouse provides 16 times the memory per node of a standard warehouse by default. Create one with WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'.
CREATE WAREHOUSE ml_wh WITH
WAREHOUSE_SIZE = 'MEDIUM'
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
RESOURCE_CONSTRAINT = 'MEMORY_16X';
| RESOURCE_CONSTRAINT | Memory per node (up to) | Smallest size |
|---|---|---|
| MEMORY_1X | 16 GB | X-Small |
| MEMORY_16X (default) | 256 GB | Medium |
| MEMORY_64X | 1 TB, preview and AWS only | Large |
The x86 variants of each setting choose the x86 CPU architecture. Use these warehouses for ML model training, large UDF or stored procedure workloads with big in memory data, and Snowpark jobs that spill on a standard warehouse. Workloads that do not use Snowpark code generally gain little.
| Aspect | Standard warehouse | Snowpark-optimized |
|---|---|---|
| Memory per node | Baseline | 16 times by default |
| Best for | SQL queries and ETL | Memory heavy Snowpark, ML and UDF code |
| Credit rate | Standard | Higher for the same size |
| Startup | Fast | Can take longer to create and resume |
Do not move ordinary SQL workloads to a Snowpark-optimized warehouse hoping for speed. They pay a higher rate for memory they do not use.
Choosing between the options
| Situation | Best choice |
|---|---|
| Rare large scans on a busy shared warehouse | Query acceleration service |
| Every report is heavy | Larger warehouse |
| Queries wait in a queue | Multi-cluster warehouse |
| Python ML training runs out of memory | Snowpark-optimized warehouse |
| Same selective lookups over a huge table | Search optimization service or clustering |
Common mistakes
Enabling QAS on every warehouse. It adds serverless cost and only helps eligible, scan heavy queries.
Setting the scale factor to 0 to be safe. Zero removes the cap on serverless compute, which can raise cost.
Using QAS to fix concurrency. It speeds up individual queries, not queues.
Choosing Snowpark-optimized for plain SQL. It is for memory bound Snowpark workloads.
What to remember for the exam
- QAS: Enterprise or higher, serverless,
ENABLE_QUERY_ACCELERATIONandQUERY_ACCELERATION_MAX_SCALE_FACTOR(default 8, 0 is unlimited). - Check eligibility with
SYSTEM$ESTIMATE_QUERY_ACCELERATIONor theQUERY_ACCELERATION_ELIGIBLEview. - QAS fits large selective scans among mixed workloads, not small scans, unselective filters or non deterministic queries.
- Snowpark-optimized warehouses give 16 times the memory per node by default and use
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'. RESOURCE_CONSTRAINTchooses the memory tier; MEMORY_16X is the default and needs size Medium or larger.
Check yourself
A shared warehouse is fine except for rare huge scans. What feature fits?
Query acceleration service, which offloads part of those scans to serverless compute without resizing the warehouse.
How do you cap the extra cost of QAS?
Set
QUERY_ACCELERATION_MAX_SCALE_FACTOR; the default is 8 and 0 removes the limit.A Python training job spills on a Large standard warehouse. What is the likely fix?
A Snowpark-optimized warehouse, which provides far more memory per node.
Topic 3 of 9 in Performance Optimization
Test yourself on Performance Optimization
The full Snowflake ARA-C01 question bank has 687 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.