Snowflake Warehouse Performance Tuning: Query Profile, Spilling, Queuing, QAS and Snowpark-Optimized Warehouses

1.Diagnose before you tune: Query History, Query Profile and warehouse load

Tuning starts with evidence. In Snowsight, Monitoring » Query History has a Duration column that you can sort to find the longest-running queries. You can filter it by user or by warehouse. Once you have a slow query, open its Query Profile. It shows which parts of the query took the longest, and it shows which operator nodes are spilling data to storage. The sources used for this lesson don't list individual operator names or exact Query Profile thresholds. What they do teach is how to read the profile: find the nodes that take the most time or spill, then match each one to a lever.

Query-level evidence doesn't show warehouse-level problems. For those, go to Compute » Warehouses and open the Warehouse Activity chart, which shows warehouse load and whether queries were queued. You can also analyse history in bulk by querying the SNOWFLAKE.ACCOUNT_USAGE views, such as QUERY_HISTORY. By default only ACCOUNTADMIN has the privileges to run those diagnostic queries.

SELECT query_id, SUBSTR(query_text, 1, 50) partial_query_text, user_name, warehouse_name,
  bytes_spilled_to_local_storage, bytes_spilled_to_remote_storage
FROM  snowflake.account_usage.query_history
WHERE (bytes_spilled_to_local_storage > 0
  OR  bytes_spilled_to_remote_storage > 0 )
  AND start_time::date > dateadd('days', -45, current_date)
ORDER BY bytes_spilled_to_remote_storage, bytes_spilled_to_local_storage DESC
LIMIT 10;

Interpreting a Query Profile follows three steps: find the bottleneck, classify it, then recommend a change.

1. Find the bottleneck. The Query Profile shows the query execution plan as operator nodes. Each node shows its operator type and the time it used, as a percentage of the query duration. The Most Expensive Nodes pane lists the operator nodes that take the longest, so start there. The profile's information panes are Profile Overview, Query Insights, Statistics, Most Expensive Nodes and Attributes. Query Insights gives a general recommendation when a condition affects performance. You can also read the same statistics in SQL with the GET_QUERY_OPERATOR_STATS table function.

2. Classify it. Execution time is broken into Processing, Local Disk IO, Remote Disk IO, Network Communication, Synchronization and Initialization. Time in Processing means the CPU was busy. Time in the disk IO categories means processing was blocked waiting on disk. The Statistics pane adds spilling counters (bytes spilled to local and remote storage), IO figures such as the percentage scanned from cache, and Pruning figures: partitions scanned against partitions total.

3. Recommend. Match the signal to a lever, then re-run the query and compare its execution time.

Virtual warehouse configuration is where most of these recommendations are applied. The main settings are:

- Type: Standard or Snowpark-optimized. - Size: the compute available per cluster. It can be changed at any time, even while the warehouse runs. - AUTO_SUSPEND: seconds of inactivity before the warehouse suspends. The default is 600, or 10 minutes. 0 or NULL means it never suspends, which is not recommended unless the workload needs a continually running warehouse. The background process that suspends warehouses runs approximately every 30 seconds, so the setting doesn't give precise control, and a value below 30 or not a multiple of 30 might not behave as expected. Suspending drops the warehouse cache. Snowflake's guideline is about 5 minutes for DevOps, DataOps and data science use cases, and at least 10 minutes for BI and SELECT query warehouses. Match the value to the gaps in your workload: a warehouse that runs a query every 30 minutes gains nothing from a 10-minute setting. - AUTO_RESUME: defaults to TRUE, so the warehouse starts when a query is submitted. Snowflake recommends leaving it disabled if you wish to control costs or user access, and resuming the warehouse manually only when needed. With it enabled, expect a short delay on resume while compute is provisioned. - MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT: a value above 1 for MAX_CLUSTER_COUNT makes it a multi-cluster warehouse. Equal values mean Maximized mode, and a lower minimum means Auto-scale mode. SCALING_POLICY (STANDARD or ECONOMY) applies in Auto-scale mode. - ENABLE_QUERY_ACCELERATION: turns on the query acceleration service. - RESOURCE_MONITOR: caps the credits the warehouse can use.

ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = 600;

Clustering is the storage-side lever the profile's pruning figures point to. Snowflake generally produces well-clustered data, which is its natural clustering. Over time, particularly as DML occurs on very large tables, the data might no longer cluster well on the dimensions you query. A clustering key is a subset of columns, or expressions, that you designate to co-locate rows in the same micro-partitions. Queries that filter or sort on the key then skip data that doesn't match.

Auto-clustering is the maintenance Snowflake performs for you. Once a key is defined, no further administration is needed, and Snowflake performs all future maintenance to keep the table clustered. It doesn't re-cluster immediately: Snowflake only performs automated maintenance if the table will benefit. That maintenance uses compute resources that consume credits, so cluster only when queries will benefit substantially. Clustering fits best when the table is very large (multiple terabytes), the queries are selective or sort, most queries use the same few columns, and the table is queried often but changes rarely. Snowflake recommends a maximum of 3 or 4 columns per key. A key can be set with CREATE TABLE or ALTER TABLE, and altered or dropped at any time. For selective point lookups on columns that aren't a good clustering key, the search optimization service is the alternative.

Sources12345678

2.Memory spillage and scaling up

When a warehouse runs out of memory during a query, the data spills to local disk, and performance drops sharply. If the query needs even more memory, the data spills to remote cloud-provider storage, which is slower still. That means remote spill is the more serious signal. Snowflake recommends two remedies. You can use a larger warehouse, which gives the operation more memory and local storage, or you can process the data in smaller batches. Adding clusters doesn't help here, because each query still runs with one cluster's memory.

Scaling up is cheap to try. You can resize a warehouse at any time, even while it is running. Each size step doubles the hourly credit rate, but billing is per second, with a 60-second minimum each time the warehouse starts. That means a larger warehouse that finishes the query faster doesn't necessarily cost more in total. After resizing, rerun the query and compare its execution time.

Sources39

3.Queuing and scaling out with multi-cluster warehouses

Queuing is a different problem from spilling. Each query might run fine once it starts, but it has to wait in a queue before starting, and that wait adds to the time before the user gets results. Warehouse query load measures the average number of queries that were running or queued over an interval, so it is the metric to watch for this symptom.

A bigger warehouse isn't the natural fix for concurrency. The documented alternative is to scale out: a multi-cluster warehouse adds capacity without changing the warehouse size. Multi-cluster warehouses require the Enterprise Edition of Snowflake, and a MAX_CLUSTER_COUNT above 1 can only be set on Enterprise Edition or higher. A multi-cluster warehouse scales compute to meet user and query concurrency as demand changes, for example between peak and off-peak hours. In Auto-scale mode, Snowflake starts and shuts down clusters as load changes. A scaling policy controls this. The default Standard policy favours starting more clusters over saving credits: when a query is queued, or when the running clusters can't take more work, Snowflake adds a cluster. The Economy policy conserves credits by keeping running clusters fully loaded. It starts a new cluster only if the system estimates enough query load to keep that cluster busy for at least 6 minutes, and it marks the least-loaded cluster for shutdown when it has less than 6 minutes of work left. The trade-off is that queries may be queued and take longer to complete. Scaling policies apply only in Auto-scale mode. In Maximized mode (minimum equals maximum) all clusters run at the same time.

You can also reduce contention by limiting how many queries run concurrently in the warehouse, so fewer queries compete for its resources.

Sources101191213

4.Query acceleration service for outlier queries

Some warehouses mostly run well-behaved queries plus an occasional outlier: a query that uses far more resources than typical. The query acceleration service (QAS) handles these outliers by offloading portions of the query processing work to shared compute resources that the service provides. It targets two patterns. The first is large scans with an aggregation or a selective filter. The second is large scans that insert or copy many rows. A query can be ineligible for several reasons: the scan has too few partitions, the filters aren't selective enough, the GROUP BY cardinality is too high, or the query uses nondeterministic functions such as RANDOM.

You turn it on with ENABLE_QUERY_ACCELERATION = TRUE. It is also on by default for Gen2 standard warehouses and multi-cluster warehouses. Two metadata tools tell you whether QAS is worth enabling. The SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE view ranks queries and warehouses by eligible execution time. The SYSTEM$ESTIMATE_QUERY_ACCELERATION function takes a query ID and returns estimated run times at different scale factors.

{
  "estimatedQueryTimes": {
    "1": 171,
    "10": 115,
    "2": 152,
    "4": 133,
    "8": 120
  },
  "ineligibleReason": null,
  "originalQueryTime": 300.291,
  "queryUUID": "8cd54bf0-1651-5b1c-ac9c-6a9582ebd20f",
  "status": "eligible",
  "upperLimitScaleFactor": 10
}

Cost is controlled by QUERY_ACCELERATION_MAX_SCALE_FACTOR, which multiplies the warehouse's size and cost. For example, a Medium warehouse costs 4 credits per hour. With a scale factor of 5, it can lease up to 5 times its size, which can cost up to 20 extra credits per hour. QAS is billed per second, only while it is in use, and separately from warehouse usage. The default scale factor is 8 when you enable QAS explicitly and 2 when it is enabled implicitly on a Gen2 or multi-cluster warehouse. Setting it to 0 removes the upper bound.

CREATE WAREHOUSE my_wh WITH  ?  = true;

Sources6

5.Snowpark-optimized warehouses for memory-heavy code

Warehouses come in two types, Standard and Snowpark-optimized. A Snowpark-optimized warehouse lets you configure the memory and CPU architecture of a single-node instance. By default it provides 16x memory per node compared with a standard warehouse. It is the recommended choice for Snowpark code with large memory needs or a dependency on a specific CPU architecture, such as ML training in a stored procedure on a single warehouse node. UDF and UDTF workloads can also benefit. Workloads that don't use Snowpark might not benefit. One trade-off: creating or resuming a Snowpark-optimized warehouse can take longer than a standard one.

CREATE OR REPLACE WAREHOUSE snowpark_opt_wh WITH
  WAREHOUSE_SIZE = 'MEDIUM'
  WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED';

Sources14

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Adding clusters to a multi-cluster warehouse fixes a query that spills to remote storage.Why is that wrong?

   Spilling means a single query lacks memory. The documented fixes are a larger warehouse size or processing data in smaller batches. Extra clusters address concurrency.

   Covered in Memory spillage and scaling up

2. 2.The QAS scale factor always defaults to 8.Why is that wrong?

   It is 8 when QAS is enabled explicitly, but 2 when Snowflake enables it automatically for Gen2 or multi-cluster warehouses at creation.

   Covered in Query acceleration service for outlier queries

3. 3.After you define a clustering key, you must re-cluster the table manually, and it is re-clustered at once and for free.Why is that wrong?

   Snowflake maintains the clustering automatically, only when the table will benefit, and that maintenance consumes credits.

   Covered in Diagnose before you tune: Query History, Query Profile and warehouse load