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
In a nutshell
  • 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.

Slow query reporteduser complaintfind it inQuery HistorySnowsight or QUERY_HISTORYopenQuery Profileoperator tree and statisticsdecideFixrewrite, resize, cluster, cache

How it works

Where to monitor

SourceWhat it givesGood to know
Snowsight Query HistoryPer query status, duration, warehouse, userOpen a row to reach its Query Profile tab
INFORMATION_SCHEMA.QUERY_HISTORY table functionRecent queries, no latencyCovers a short recent window only
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY viewLong history, all queries in the accountHas latency; keeps 365 days
WAREHOUSE_LOAD_HISTORYRunning, queued and blocked queries per warehouseShows whether a warehouse is overloaded
WAREHOUSE_METERING_HISTORYCredits per warehouseLinks 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.
Exam tip

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.

  1. Look at the Most Expensive Nodes pane to find the operator that took the largest share of time.
  2. Read the Profile Overview to see how time splits across categories.
  3. Check the Statistics pane for bytes scanned, partitions scanned against total, and spilling.
  4. Match what you see to a known problem and fix it.

Components of the Query Profile

ComponentWhat it shows
Query execution planOperator nodes joined by arrows that show the rows flowing between them
Operator nodeOperator type and ID, percentage of total query time, and a preview such as the table name
Navigation controlsMove between steps (only when the query ran in steps), fit to window, zoom
Profile OverviewExecution time split into categories
Query InsightsSuggestions Snowflake raises about the query
StatisticsIO, DML, Pruning, Spilling and Network counters
Most Expensive NodesThe nodes that used the most time
AttributesDetails 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.
Profile Overviewtime categoriesnarrow toMost Expensive Nodeswhich operatorconfirm inStatisticsscanned, pruned, spilledactFix

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_HISTORY keeps long history but has latency; the INFORMATION_SCHEMA function 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.