Performance Services
Four managed services sit on top of warehouses, each fixing one specific slowness pattern. The skill is matching the symptom to the service, so this chapter is organized symptom-first.
The master decision, before any money is spent
The whole diagnostic is one path. Walk it top to bottom, and stop at the first box that matches:
Missing or wrong join condition. Free, and no warehouse or service will save a Cartesian product.
Memory doubles with each size. Remote spill means go up now, not eventually.
Dates, regions, slices of the table. Physical reordering serves range predicates.
Needle in haystack: user IDs, order numbers, substrings. The access path, not reordering.
Single table, deterministic, read constantly, written rarely. Pay refresh once, read cheap forever.
The outlier queries borrow serverless compute; the warehouse stays sized for the median.
Order matters: the SQL fix is free, the resize is one number, the four services each add a permanent line to the bill. The right answer is the cheapest fix that actually addresses the symptom, and Youness agrees when the invoice arrives.
That ordering is worth making explicit as a repair ladder. First read the profile, because guessing buys the wrong fix. Second, repair the query itself: join conditions, filters that were meant to exist, a SELECT * feeding a 40-column scan nobody needed. Third, check whether the warehouse is merely mis-sized, one ALTER away. Only fourth come the services, because every one of them bills continuously: clustering pays recluster credits on every load, SOS pays storage plus maintenance forever, MVs pay refresh on every base change, QAS pays per accelerated second. At IsmaFrost, Thomas's rule is that a service proposal comes with the profile screenshot that justifies it, which kills most proposals at step two.
Reading the Query Profile
Before buying any optimization, diagnose. The Query Profile (Snowsight → Query History → click a query → Query Profile) shows the execution plan as a tree of operators, each annotated with its share of total time and its statistics. The same data is queryable with GET_, useful when you want to analyze plans in SQL rather than click through them.
Example 1: the query that never seems to start
Thomas submits a query and the UI just says Queued, minute after minute, no operator tree, no statistics, nothing to read yet. He opens the Query Profile anyway, out of habit, and there's nothing there to diagnose: a queued query hasn't been given compute, so it hasn't executed a single operator.
This is the first branch on the master decision from the top of this page, and it's worth stating plainly: the problem is the warehouse, not the query. The SQL might be perfectly written; it simply hasn't been let in yet, because the warehouse's cluster is already running MAX_CONCURRENCY_LEVEL statements (8 by default) and everything past that number waits in line. Nothing about rewriting the query, clustering a table, or enabling SOS touches this, because none of those tools run until the warehouse admits the statement. The fix lives back in Scaling Warehouses: more clusters (multi-cluster, Auto-scale) if this happens at predictable busy times, or simply waiting if it's a one-off spike. Resizing the warehouse doesn't help either, since a bigger cluster still runs one admission queue; scaling out is what adds lines.
Example 2: the query that ran, slowly
A different query from Thomas does start, finishes, and this time the Query Profile is full: an operator tree, a statistics panel, numbers everywhere. Say it shows partitions scanned equal to partitions total, and 40 GB spilled to local storage. That's not a warehouse problem anymore, the warehouse admitted and ran it, it's a performance problem inside the query's own execution, and each symptom below points at one of the Performance Services from the previous chapters:
| What the profile shows | Where the fix lives |
|---|---|
| Partitions scanned ≈ partitions total | Clustering Keys & Automatic Clustering |
| Selective point lookups still scanning everything | Search Optimization Service |
| A single huge scan feeding an aggregation, no time to reorganize the table | Query Acceleration Service |
| Bytes spilled to local or remote storage | Resize the warehouse (Virtual Warehouses chapter), the one fix that lives in warehouse sizing rather than Performance Services |
The two examples together give the whole master decision in miniature: if the query hasn't started, look at the warehouse; if it ran and was still slow, look at the profile's own statistics and match the symptom to a service.
The statistics panel
Four numbers in the profile's side panel carry almost all the diagnostic weight.
| Statistic | What it tells you | The fix it points to |
|---|---|---|
| Partitions scanned vs partitions total | Scanned ≈ total means pruning failed: the filter column's values are scattered across every micro-partition | Clustering key or, for point lookups, the Search Optimization Service |
| Bytes spilled to local storage | Working set exceeded memory; the node fell back to its SSD. A slowdown | Larger warehouse (memory doubles per size) |
| Bytes spilled to remote storage | Even the SSD overflowed; now paging to object storage. A cliff, often 10x slower | Larger warehouse, urgently |
| Percentage scanned from cache | How much of the scan came from the warehouse's local disk cache rather than remote storage | High is good; drops to zero after suspend and resume, and partly after a downsize |
"Percentage scanned from cache" refers to the warehouse's local SSD cache, not the result cache. A result-cache hit never reaches the profile as a scan at all: the profile shows a single QUERY RESULT REUSE node with no operator tree, because no warehouse did any work.
Reading the operator tree
Operators are the usual suspects: TableScan (reads micro-partitions, where pruning shows up), Filter, Join, Aggregate, Sort, and Result. Two patterns matter most.
One operator dominating total time. The profile attributes execution time per operator. A TableScan at 95% means the query is I/O bound and pruning is the lever. A Sort or Aggregate dominating alongside spill bytes means memory is the lever.
An operator emitting far more rows than its inputs. A join producing 400M rows from two 20K-row inputs is an exploding join, almost always a missing or wrong join condition producing a Cartesian product. The fix is the SQL, not the warehouse. A query with no join criteria at all always explodes.
A concrete example of reading the profile
A dashboard tile joins a 40M-row fact table to a small dimension, filtered on a scattered column, and takes forever. Start where the profile starts you: the two TableScans, side by side.
OPERATORJoin · 9%continues up the tree
- 1,200 rowsTABLESCAN · 2% OF TIMEDIM_CUSTOMER1 of 1 partition · 1,200 rowspartitions scanned: 100%✓tiny table, full read is right
- 39.8M rowsTABLESCAN · 71% OF TIMESALES_FACT8,140 of 8,200 partitions · 39.8M rowspartitions scanned: 99%✗pruning failed on the filter
Percentages are shares of execution time (illustrative).
Reading them together is the diagnostic. The dimension scan is what health looks like: partitions scanned matches what the query needs. The fact scan reads 99% of the table to satisfy a filter that will discard almost everything, which means the metadata couldn't exclude anything: the filter column's values are scattered across every partition. That symptom has one name (pruning failure) and one cheap fix (a clustering key on the filter column), and no warehouse size changes it.
The rest of the tree confirms a second, separate problem:
So the profile hands you two findings with two different price tags. The scan problem is fixed by clustering (cheap, targeted, permanent). The spill problem is fixed by a larger warehouse (direct, but doubles the rate). A bigger warehouse would also mask the scan problem, since more nodes reading garbage in parallel still finish sooner, and that's exactly the trap: the cheaper fix that addresses the cause beats the expensive one that happens to also work. Diagnose per operator, fix per symptom, spend last.
Where query history lives
Three places, with different retention and latency.
-- INFORMATION_SCHEMA table function: last 7 days, zero latency SELECT * FROM TABLE(INFORMATION_ SCHEMA. QUERY_ HISTORY( result_ limit => 100)); -- ACCOUNT_USAGE view: 365 days of history, up to 45 minutes behind SELECT query_id, warehouse_name, bytes_ spilled_ to_ remote_ storage FROM SNOWFLAKE.ACCOUNT_ USAGE. QUERY_ HISTORY WHERE bytes_spilled_ to_ remote_ storage > 0; -- Operator-level profile data in SQLSELECT * FROM TABLE(GET_ QUERY_ OPERATOR_ STATS( LAST_ QUERY_ ID( )));
Four symptoms, four fixes. Scanning everything → clustering. Spilling → bigger warehouse. Exploding rows → fix the join. Queuing (from the warehouse pages) → more clusters. Every slow-query symptom maps to one of these pairings or to a service in this chapter.
Two profiles that confuse people the first time
Run a query twice and open the second run's profile: a single node labeled QUERY RESULT REUSE, no operators, no statistics. Nothing broke; the result cache answered, and there was no execution to profile. The other head-scratcher is a profile whose Initialization or compilation time dwarfs execution. That points at cloud services, not the warehouse: thousands of files in a stage scan, an enormous IN list, or view-on-view-on-view definitions the optimizer must expand. Resizing the warehouse does nothing for either pattern, which is why "scale up" is the wrong answer for them.
In Snowsight, the Monitoring tab exposes Query History and Copy History to users directly, no ACCOUNT_USAGE privileges needed, which is where Alice checks her own slow dashboards without asking Youness for anything.
- Partitions scanned ≈ partitions total means pruning failed. Spill bytes mean memory ran out. Row explosion means broken join logic.
- Cache percentage in the profile is the local disk cache; result-cache hits skip the warehouse entirely and show no operators.
- INFORMATION_SCHEMA.QUERY_HISTORY: 7 days, live. ACCOUNT_USAGE.QUERY_HISTORY: 365 days, up to 45 minutes of latency.

