How to Use Query Profile: Snowflake Query Performance Analysis Guide

Query Profile is Snowflake's performance analysis tool that visually surfaces a query's execution plan and runtime statistics. In the Snowsight UI, you can inspect processing time, partitions scanned, and spill volume for each operator to pinpoint bottlenecks.

This article walks through how to read Query Profile in the Snowsight UI, how to identify Most Expensive Nodes, how to check partition pruning, how to analyze spill, and a step-by-step bottleneck identification flow. It is a frequently tested topic in the Performance Concepts domain of the SnowPro exams.

How to Access Query Profile

You inspect Query Profile in Snowsight (the web UI).

1. From the Snowsight left menu, open Activity → Query History
2. Search for the query you want to analyze and click it
3. Select the Query Profile tab to display the DAG (directed acyclic graph) view

When you run SQL from a Worksheet, you can jump straight in via the Query Profile link next to the results tab. To pull Query Profile statistics from SQL, use the GET_QUERY_OPERATOR_STATS() table function.

-- Retrieve Query Profile statistics from SQL
SELECT *
FROM TABLE(GET_QUERY_OPERATOR_STATS('01b2c3d4-0001-abcd-0000-00012345abcd'));

Components of the DAG View

Query Profile shows each processing step as an operator (node) and connects them with arrows representing data flow, producing a DAG (directed acyclic graph).

Identifying Most Expensive Nodes

Snowsight shows each node's share of total query processing time as a percentage and highlights the nodes that consumed the most resources in color.

Common Bottleneck Patterns

Checking Partition Pruning

Partition pruning is the optimization that skips reading unnecessary micro-partitions based on WHERE clause conditions. In Query Profile, check the following values on the TableScan operator.

• Partitions Total — Total number of partitions in the table
• Partitions Scanned — Number of partitions actually scanned

Partitions Scanned / Partitions Total — the lower this ratio, the better pruning is working. When the ratio is close to 100%, setting a clustering key is effective.

-- Example: verifying pruning effectiveness
-- Set a clustering key on the column used in WHERE
ALTER TABLE large_events CLUSTER BY (event_date);

-- Check clustering state
SELECT SYSTEM$CLUSTERING_INFORMATION('large_events', '(event_date)');

Spill Analysis

Spill happens when data that does not fit in memory during query execution gets written out to disk. It is one of the biggest causes of performance degradation.

On each Query Profile operator, check the values of Bytes Spilled to Local Storage and Bytes Spilled to Remote Storage. If Remote Spill is occurring, sizing up the warehouse is the top-priority fix.

Bottleneck Identification Flow

1. Open Query Profile — select the target query from Snowsight Query History or a Worksheet
2. Inspect Most Expensive Nodes — locate the color-highlighted nodes in the DAG view
3. Check whether spill is occurring — verify whether Bytes Spilled to Local/Remote Storage is non-zero
4. Check partition pruning — check the Partitions Scanned vs Total ratio on TableScan
5. Apply countermeasures — apply one of: size-up, clustering key, or query optimization
6. Verify the improvement — re-run the same query and confirm the improvement in Query Profile

Query Analysis via SQL

-- Top 10 longest-running queries in the last 24 hours
SELECT query_id, query_text, total_elapsed_time / 1000 AS elapsed_sec,
       bytes_spilled_to_local_storage, bytes_spilled_to_remote_storage,
       partitions_scanned, partitions_total
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD(hour, -24, CURRENT_TIMESTAMP())
  AND execution_status = 'SUCCESS'
ORDER BY total_elapsed_time DESC
LIMIT 10;

Sample Question

Frequently Asked Questions