Snowflake Query Profile

Slow queries frustrate users and burn credits. Guessing the cause wastes time. Snowflake records every query and draws a visual map of how it ran. Reading that map turns performance tuning from guesswork into a clear, repeatable routine.

Where to Find the Tools

Open Snowsight, choose Monitoring, and select Query History. The page lists recent queries with status, duration, user, and warehouse. Click any row to see its details and open the Query Profile tab. Think of Query History as a receipt drawer and Query Profile as a detailed itemized bill for one purchase.

Reading the Operator Tree

A query runs as a chain of steps called operators. The profile draws them as boxes connected by arrows. Data flows from the bottom boxes to the top.

SAMPLE QUERY PROFILE
====================

SELECT region, SUM(amount) FROM sales
JOIN stores ON ... WHERE sale_date >= '2026-01-01' GROUP BY region

   +------------------+
   | Result           |  returns final rows
   +------------------+
            ^
   +------------------+
   | Aggregate        |  SUM by region            12%
   +------------------+
            ^
   +------------------+
   | Join             |  matches sales to stores  58%  <-- most expensive
   +------------------+
        ^         ^
 +-----------+  +-----------+
 | TableScan |  | TableScan |
 | sales     |  | stores    |
 | 40 of 900 |  | 1 of 1    |
 | partitions|  | partition |
 +-----------+  +-----------+

Each box shows the percentage of total time spent. Snowsight highlights the most expensive nodes so attention goes straight to the biggest problem.

Key Statistics and What They Reveal

STATISTIC                         WHAT IT MEANS                         ACTION
--------------------------------  ------------------------------------  -------------------------
Partitions scanned vs total       How well pruning skipped data         Add clustering or filters
Percentage scanned from cache     Share of data read from warehouse     Keep warehouse running for
                                  cache                                 repeated work
Bytes spilled to local storage    Memory ran out, disk used             Consider a larger warehouse
Bytes spilled to remote storage   Disk ran out too, very slow           Use a larger warehouse
Rows produced by Join             Output far above input = exploding    Check join keys for
                                  join                                  duplicates

Common Problems and Fixes

Scanning Too Many Partitions

A TableScan that reads 900 of 900 partitions means filters failed to prune data. Filter on columns that match the table's natural order, such as a date column. Large tables benefit from clustering keys.

Exploding Joins

A join that outputs millions of rows from thousands of inputs signals duplicate keys. Check uniqueness on the join columns and remove duplicates before joining.

Spilling to Disk

Spilling means the warehouse ran out of memory for a sorting or joining step. Local spilling slows a query moderately. Remote spilling slows it severely. A larger warehouse or a smaller dataset solves both.

Queuing

A query that waits before running points to an overloaded warehouse. Multi-cluster mode or a separate warehouse removes the wait.

Finding Slow Queries With SQL

SELECT query_id,
       user_name,
       warehouse_name,
       total_elapsed_time / 1000      AS seconds,
       partitions_scanned,
       partitions_total,
       bytes_spilled_to_local_storage AS local_spill,
       bytes_spilled_to_remote_storage AS remote_spill
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP())
  AND total_elapsed_time > 60000
ORDER BY total_elapsed_time DESC
LIMIT 20;

The query lists the twenty slowest statements from the last day that ran longer than one minute. Account Usage views carry a short delay, so very recent queries may not appear yet.

A Simple Tuning Routine

1. Open the slow query in Query History
2. Open Query Profile
3. Find the most expensive node
4. Read its statistics
5. Apply one fix
6. Run again and compare the time
7. Keep the change only when the time improves

A Practical Scenario

A finance dashboard takes four minutes to load. The analyst opens Query History, finds the dashboard query, and opens its profile. The scan node reads almost every partition of a large sales table because the filter targets a text column that matches no stored order. The analyst rewrites the filter to use the sale date column, and the scan drops to a small fraction of the partitions. The dashboard now loads in seconds with the same warehouse size.

Frequently Asked Questions

Does a bigger warehouse always fix a slow query?

A bigger warehouse helps with spilling and heavy joins. It does not fix poor pruning or exploding joins, which need query or table changes.

Who can see Query Profile?

Users see profiles for their own queries. Administrators and roles with monitoring privileges see queries from other users.

Key Points

• Query History lists every query; Query Profile explains how one query ran.
• The operator tree shows each step and its share of total time.
• Partitions scanned against total reveals how well pruning works.
• Spilling to disk signals memory pressure on the warehouse.
• Exploding joins usually come from duplicate join keys.
• Change one setting at a time and measure the result.