Performance Monitoring and Tuning · Topic 1 of 16
Monitoring Performance and Reading the Query Profile
Monitor Snowflake performance with Query History, ACCOUNT_USAGE and INFORMATION_SCHEMA, then read the Query Profile to find the slow step and its cause.
- 4 min
- Start in Query History, then open the Query Profile of the slow query.
- The profile is an operator tree with a percentage of time on each node, plus Statistics for IO, DML, Pruning, Spilling and Network.
- Compare partitions scanned with partitions total to judge pruning; check both spill counters for memory pressure.
What it is
Performance monitoring answers two questions: which queries are slow or expensive, and why. The administrator starts wide, with history and usage views, then goes narrow, into one query's Query Profile.
The Query Profile is a graphical execution plan of a query that already ran. It shows where time went, how much data moved, and whether the warehouse had to spill to disk. On the exam you are given a symptom and asked what the profile would show, or which action fixes it.
How it works
Where to monitor
| Source | What it gives | Good to know |
|---|---|---|
| Snowsight Query History | Per query status, duration, warehouse, user | Open a row to reach its Query Profile tab |
INFORMATION_SCHEMA.QUERY_HISTORY table function | Recent queries, no latency | Covers a short recent window only |
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view | Long history, all queries in the account | Has latency; keeps 365 days |
WAREHOUSE_LOAD_HISTORY | Running, queued and blocked queries per warehouse | Shows whether a warehouse is overloaded |
WAREHOUSE_METERING_HISTORY | Credits per warehouse | Links performance to cost |
Useful columns in QUERY_HISTORY are TOTAL_ELAPSED_TIME, COMPILATION_TIME, EXECUTION_TIME, QUEUED_OVERLOAD_TIME, QUEUED_PROVISIONING_TIME, PARTITIONS_SCANNED, PARTITIONS_TOTAL, BYTES_SPILLED_TO_LOCAL_STORAGE and BYTES_SPILLED_TO_REMOTE_STORAGE.
SELECT query_id, warehouse_name, total_elapsed_time,
bytes_spilled_to_remote_storage,
partitions_scanned, partitions_total
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY total_elapsed_time DESC
LIMIT 20;
Reading the time columns tells you where to look first.
- High queued time means the warehouse is saturated: scale out with a multi-cluster warehouse or split the workload.
- High compilation time points at very complex SQL or many objects.
- High execution time sends you into the Query Profile.
A scenario that says "queries wait before they start" is a queuing problem. Resizing the warehouse up does not add concurrency; extra clusters do.
Evaluating a Query Profile
Open Query History in Snowsight, choose a query and open the Query Profile tab. Do a quick evaluation in this order.
- Look at the Most Expensive Nodes pane to find the operator that took the largest share of time.
- Read the Profile Overview to see how time splits across categories.
- Check the Statistics pane for bytes scanned, partitions scanned against total, and spilling.
- Match what you see to a known problem and fix it.
Components of the Query Profile
| Component | What it shows |
|---|---|
| Query execution plan | Operator nodes joined by arrows that show the rows flowing between them |
| Operator node | Operator type and ID, percentage of total query time, and a preview such as the table name |
| Navigation controls | Move between steps (only when the query ran in steps), fit to window, zoom |
| Profile Overview | Execution time split into categories |
| Query Insights | Suggestions Snowflake raises about the query |
| Statistics | IO, DML, Pruning, Spilling and Network counters |
| Most Expensive Nodes | The nodes that used the most time |
| Attributes | Details for the node you select |
The execution time categories are Processing, Local Disk IO, Remote Disk IO, Network Communication, Synchronization, Initialization and, for hybrid tables, Hybrid Table Requests Throttling. A profile dominated by Remote Disk IO suggests spilling or heavy reads from storage, while a large Processing share means the CPU work itself is the cost.
The statistics groups are:
- IO: scan progress, bytes scanned, percentage scanned from cache, bytes written, bytes written to result, bytes read from result, external bytes scanned.
- DML: rows inserted, updated, deleted and unloaded.
- Pruning: partitions scanned and partitions total.
- Spilling: bytes spilled to local storage and bytes spilled to remote storage.
- Network: bytes sent over the network.
Common mistakes
Tuning from elapsed time alone. Elapsed time mixes queue, compile and run time. Split them before you change anything.
Reading the profile of a cached result. A query served from the result cache has almost no plan to inspect.
Treating INFORMATION_SCHEMA and ACCOUNT_USAGE as the same. The first is recent and immediate, the second is long and delayed. A later page of this chapter covers the difference.
Fixing the biggest node by habit. The most expensive node may only be slow because an earlier step sent it too many rows.
What to remember for the exam
- Start in Query History, then open the Query Profile of the slow query.
- The profile is an operator tree with a percentage of time on each node, plus Statistics for IO, DML, Pruning, Spilling and Network.
- Compare partitions scanned with partitions total to judge pruning; check both spill counters for memory pressure.
ACCOUNT_USAGE.QUERY_HISTORYkeeps long history but has latency; theINFORMATION_SCHEMAfunction is current but short.- Queue time is a concurrency problem, not a query problem.
Check yourself
Where do you find which operator used most of a query's time?
In the Query Profile, in the Most Expensive Nodes pane or by the percentage on each operator node.
Which two statistics show memory pressure?
Bytes spilled to local storage and bytes spilled to remote storage.
Queries sit in the queue before they run. Does a bigger warehouse fix it?
Not by itself; a multi cluster warehouse or splitting the workload adds concurrency.
Topic 1 of 16 in Performance Monitoring and Tuning
Test yourself on Performance Monitoring and Tuning
The full Snowflake ADA-C02 question bank has 814 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.