Performance Optimization · Topic 1 of 9
Performance Tools, Query Profile and Metadata Functions
Pick the right Snowflake performance tool, read a Query Profile for spilling, pruning and exploding joins, and use history and metadata functions.
- 5 min
- Query Profile shows operator tree, spilling, pruning and IO statistics; most expensive nodes are highlighted.
- Partitions scanned near total means poor pruning; spilling means memory shortage; output far above input means an exploding join.
GET_QUERY_OPERATOR_STATSgives profile data as rows;EXPLAINshows the plan without running.
What it is
Snowflake gives an Architect a set of tools, and the skill the exam tests is matching a symptom to the right one. The Query Profile shows what a single query did. Query history shows patterns across queries. Metadata functions report on tables, clustering and plans without running the workload again.
Each tool answers a different question, so start from the symptom: slow, queued, expensive or scanning too much.
How it works
Choosing the performance tool
| Symptom | First look | Typical fix |
|---|---|---|
| One query is slow | Query Profile | Rewrite, prune better, larger warehouse |
| Many queries wait | Warehouse load and queue columns | Multi-cluster or more warehouses |
| Big scans for selective filters | Partitions scanned versus total | Clustering key or search optimization |
| Memory spilling | Bytes spilled in the profile | Larger or Snowpark-optimized warehouse |
| Repeated identical queries | Cache use | Result cache, materialized view |
| Sporadic large scans on a busy warehouse | Query acceleration eligibility | Query acceleration service |
The Query Profile
Open a query in Snowsight and choose Query Profile. It shows an operator tree and statistics. Click an operator for its detail.
| Operator or section | What to read |
|---|---|
| TableScan | Partitions scanned versus total, bytes scanned, percent scanned from cache |
| Filter, Join, Aggregate, Sort | Rows in and out, and the share of time spent |
| Most expensive nodes | Highlighted by percentage of total time |
| Statistics: Spilling | Bytes spilled to local storage and to remote storage |
| Statistics: IO | Bytes read, scanned from cache, network |
| Statistics: Pruning | Partitions scanned and total |
Time is split into processing, local disk IO, remote disk IO, network communication, synchronization and initialization. A high remote disk IO share points at scanning and spilling; high processing points at compute heavy operators.
Reading the profile for bottlenecks
| Finding | Cause | Recommendation |
|---|---|---|
| Scanned partitions equal total partitions | Filter on a column that is not clustered, or the filter wraps the column in a function | Cluster on the filtered column, rewrite the predicate |
| Bytes spilled to local or remote storage | Operator needs more memory than the warehouse has | Use a larger warehouse, process in smaller pieces, or a Snowpark-optimized warehouse |
| Join returns far more rows than either input | Missing or wrong join condition produces a Cartesian product | Fix the join condition |
| UNION that is slow | UNION removes duplicates through an extra step | Use UNION ALL when duplicates are fine |
| Large Sort at the top | ORDER BY on a big result | Remove it or add LIMIT |
| Queued time very high | Warehouse concurrency limit reached | Add clusters or another warehouse |
A profile that shows spilling to remote storage is the stronger signal. Local spilling is slow; remote spilling is much slower and nearly always justifies a larger warehouse or a rewrite.
Query history and metadata functions
| Source | Use |
|---|---|
INFORMATION_SCHEMA.QUERY_HISTORY table function | Recent queries, current and near real time |
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view | Long term, whole account, with some latency |
GET_QUERY_OPERATOR_STATS(<query_id>) | The profile's operator statistics as rows, for scripting |
EXPLAIN and SYSTEM$EXPLAIN_PLAN_JSON | The plan and the partitions it expects to scan, without running the query |
SYSTEM$CLUSTERING_INFORMATION and SYSTEM$CLUSTERING_DEPTH | Clustering quality for a table and key |
SYSTEM$ESTIMATE_QUERY_ACCELERATION | Whether a query would benefit from query acceleration |
SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS | Cost estimate before enabling search optimization |
Useful query history columns are TOTAL_ELAPSED_TIME, QUEUED_OVERLOAD_TIME, QUEUED_PROVISIONING_TIME, PARTITIONS_SCANNED, PARTITIONS_TOTAL, BYTES_SPILLED_TO_LOCAL_STORAGE, BYTES_SPILLED_TO_REMOTE_STORAGE and BYTES_SCANNED.
SELECT query_id, total_elapsed_time, partitions_scanned, partitions_total,
bytes_spilled_to_remote_storage
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD('day', -1, CURRENT_TIMESTAMP())
AND bytes_spilled_to_remote_storage > 0
ORDER BY total_elapsed_time DESC
LIMIT 20;
This finds the day's worst spilling queries. A ratio of PARTITIONS_SCANNED to PARTITIONS_TOTAL near 1 on a selective query is the pruning check.
Best practices that apply broadly
- Select only the columns you need, instead of
SELECT *. - Filter early and on columns that prune; avoid wrapping filtered columns in functions.
- Use native types, not strings, for dates and numbers.
- Size by workload: small and steady for ETL, large for heavy analytics, separate warehouses per workload type.
- Let caching help before buying compute.
- Test a bigger warehouse on one slow query: if runtime halves with double credits per hour, total credits stay about the same.
Common mistakes
Adding compute first. A bad pruning ratio or an exploding join does not get fixed by a bigger warehouse.
Reading only elapsed time. Compare queued, compile, spill and scan numbers to find the real cause.
Treating ACCOUNT_USAGE as real time. It has latency; use the INFORMATION_SCHEMA table function for fresh data.
Running the query again to investigate it. EXPLAIN and query history avoid rerunning an expensive statement.
What to remember for the exam
- Query Profile shows operator tree, spilling, pruning and IO statistics; most expensive nodes are highlighted.
- Partitions scanned near total means poor pruning; spilling means memory shortage; output far above input means an exploding join.
GET_QUERY_OPERATOR_STATSgives profile data as rows;EXPLAINshows the plan without running.SYSTEM$CLUSTERING_INFORMATION,SYSTEM$ESTIMATE_QUERY_ACCELERATIONandSYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTShelp decide before enabling a feature.- Match symptom to tool before changing warehouse size.
Check yourself
A selective query scans 98 percent of the partitions in a large table. What does the profile tell you?
Pruning is poor. Consider a clustering key on the filtered column or rewriting a predicate that wraps the column in a function.
Which profile statistic suggests that a larger warehouse may help?
Bytes spilled to local or remote storage, which means operators ran out of memory.
How can you check a query plan without executing the query?
Run
EXPLAINor useSYSTEM$EXPLAIN_PLAN_JSON.
Topic 1 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.