Skip to content

Grokem · https://www.grokem.com/data/data-platforms/data-warehouses/snowflake-slow-query-with-disk-spills-read-the-query-profile-and-fix-it

Data WarehousesTroubleshooting guide

Snowflake slow query with disk spills: read the Query Profile and fix it

Find out why a Snowflake query is slow or spilling to disk by reading its Query Profile, then fix queuing, excess scans and spills in order. Each fix comes with a check that proves it worked.

Pankaj Nigam

Technical Writer

Published
  • 8 min read
  • 0 sec

Tested with
  • Snowflake
  • Snowflake Query Profile
  • Snowflake query history
Your progress
  1. Snowflake slow query with disk spills: read the Query Profile and fix it8 min(finished)
    0%

A query that used to finish quickly now crawls, or its profile shows data spilling to disk. This guide gives you a fixed order of checks. You start with the cheapest to rule out and finish with the most involved.

You will go from query history to the Query Profile, name the cause, apply one fix and prove it with a second run.

No version number applies here. Menu labels and profile wording can change between releases, so check the labels in your own Snowflake interface against the official Snowflake documentation for Query Profile.

The symptom and how to approach it

You see one of these:

  • A query takes much longer than its earlier runs.
  • A query is slow only at certain times of day.
  • The Query Profile shows disk spills.
  • A report that scans a large table is slow even when nothing else is running.

These look alike from the outside but have different causes. Fixing the wrong one wastes time and warehouse credits.

Work in this order:

  1. Rule out waiting. The query may not be slow at all, only queued.
  2. Rule out excess scanning. The query may read far more data than it needs.
  3. Check for spilling. The query may be processing more data than fits in memory.

This order puts the quickest checks first. Queuing and scanning show up in query history and the first screen of the profile. Spilling needs a closer read of the operators.

Change one thing at a time. If you change the query and the warehouse together, you cannot tell which change helped.

Step 0: Find the slow run and its context

Open query history and locate the slow run. Query history gives you visibility into all queries executed in Snowflake, so it is the right starting point for performance analysis.

Note the total duration, the warehouse and the start time. You need them later to compare runs.

Then look at warehouse load around that start time. Check this before you touch the query, because a heavily loaded warehouse slows good queries too. Keep an eye on concurrency scaling and see whether your warehouse was handling many queries at once.

Finally, find earlier runs of the same query. Group runs of the same query pattern together if your tooling allows it. One slow run among many fast ones points to load or data changes. A query that has always been slow points to its own design.

Write down the fast and slow durations. This is your baseline for the confirmation step.

How to read the Query Profile

Open the slow run and switch to its Query Profile. The profile is a visual breakdown of query execution. It lets you optimize specific stages instead of guessing at the whole query.

Read it in three passes.

Pass 1: where does the time go? Look for the stage or operator that takes the largest share of execution time. Ignore small stages. A fix that speeds up a 3 percent operator cannot rescue the query.

Pass 2: how much data moves? Compare the rows or bytes that enter an operator with what leaves it. A scan that reads a huge table and passes almost everything on to the next step means filtering is weak or late.

Pass 3: is anything spilling? Check the statistics for the query and for the heavy operators for disk spills. Spilling means the operator needed more memory than it had, so it wrote intermediate data to disk. Disk is much slower than memory.

Query historySlow runQuery ProfileQueuedcheck warehouse loadand concurrency scalingScans too muchfilter earlierDisk spillreduce data, thenmore computeRe-run and compare
Check queuing, scans and spills in that order, then re-run to confirm.

The exact names of the statistics and the way the interface lays them out vary. Check them against the official Snowflake documentation for Query Profile rather than relying on a label from memory.

Cause 1: the query is queued or the warehouse is overloaded

How to recognise it. The run was slow, but the profile shows the execution itself was not unusually long. High queuing time accounts for the gap. Warehouse load was high when the query started.

Why it happens. Too many queries were competing for the same warehouse. Your query waited for its turn before it did any work.

Fix.

  1. Compare the slow run's start time with warehouse load. If load peaked, you have found the cause. Rewriting the query will not help, because the query was not the problem.
  2. Check whether concurrency scaling is in play for that warehouse. It exists to absorb concurrent demand. Confirm how it is configured for your account.
  3. Separate workloads. Move heavy or scheduled jobs away from interactive users, or stagger their start times. This removes the contention without a bigger bill.

The settings that control scaling and the cost of each choice are not described here. Read the official Snowflake documentation on warehouses before you change them.

Confirm. Re-run at a quiet time and again at a busy time. If the quiet run matches your fast baseline and the busy run does not, contention was the cause. After the fix, the busy run should show little or no queuing.

Cause 2: the query scans more data than it needs

How to recognise it. One or more scan operators dominate execution time. They read a large amount of data, and the operators after them keep only a small part. Filtering is suboptimal: it comes too late, or not at all.

Why it happens. The query asks for broad data and narrows it later. Every extra row read costs time, and the extra rows then flow through joins and aggregations that also have to hold them.

Fix. Optimize the query to reduce unnecessary scans.

  1. Add or tighten filters so they apply to the largest table as early as possible. This shrinks the data every later step handles.
  2. Remove columns and tables you do not use. Less data per row and fewer inputs mean less work.
  3. Filter before you join, for example in a subquery or a common table expression, so the join sees fewer rows.
  4. Look for the same expensive scan repeated in one query. Compute it once if you can.

The documentation behind this guide does not describe data layout features such as clustering. If a well-filtered query still reads far too much, ask your platform owner and read the official Snowflake documentation on that topic.

Confirm. Open the new run's Query Profile. The scan operator should now read less data and take a smaller share of the time. Total duration should fall toward the baseline.

Cause 3: an operator spills to disk

How to recognise it. The profile reports disk spills on one or more heavy operators, typically a join, sort or aggregation. Those operators run slowly compared with the data they handle.

Why it happens. The operator received more data than fits in memory, so it wrote part of it to disk and read it back. Spilling is often a symptom of Cause 2, because the operator is holding rows the query never needed.

Fix, in this order.

  1. Fix scanning first. Apply the filters and column cuts from Cause 2. If the spill vanishes, you are done. This is the cheapest fix and keeps cost flat.
  2. Reduce the work inside the spilling operator. Aggregate before a join where that is logically safe. Avoid a sort over the full result unless the output needs it. Split one very large query into stages when a single step is too big.
  3. Give the query more compute. Run it on a larger warehouse, because more compute means more memory per operator. This costs more per hour of running, so treat it as the last step and measure the result. Read the official Snowflake documentation on warehouse sizing for the exact options.

Do not start with step 3. A larger warehouse can hide a badly written query and raise the cost of every future run.

Confirm. Open the Query Profile of the new run. The disk spill statistics for the operator should be gone or much smaller. Duration should drop. If it did not, the spill was not your main cost, so go back to Pass 1 and look for the real bottleneck.

Prove the fix worked

Do not rely on one lucky run. Use this routine:

  1. Run the changed query on the same warehouse at a similar level of load.
  2. Open its Query Profile and compare it with the slow run, operator by operator.
  3. Compare total duration with the fast and slow baselines you wrote down in Step 0.
  4. Check query history a few hours or days later. The fix should hold across several runs, not just the first.

If a cost spike prompted this work, look at repeated runs of the same query pattern as a group. Disk spills and queue delays are the bottlenecks that show up most when you group runs this way. Watch whether the group's average falls after your change.

Keep a short note of what you changed, the cause you found and the before and after durations. The next person who sees the same symptom will save an hour.


Troubleshooting

ProblemLikely causeFix
The query is slow, but its profile shows a short execution timeThe query waited in a queue because the warehouse was heavily loaded.Compare the start time with warehouse load in query history, check how concurrency scaling is set up, and separate or stagger heavy workloads. Re-run at a quiet and a busy time to confirm.
A scan operator takes most of the time and passes few rows onFilters are missing, too weak or applied late, so the query reads far more data than it needs.Add or tighten filters on the largest table, drop unused columns and tables, and filter before joins. Check that the scan now reads less data.
The Query Profile shows disk spills on a join, sort or aggregationThe operator handles more data than fits in memory and writes intermediate data to disk.Reduce the data first with better filtering, then restructure the operator's work, and only then try a larger warehouse. Confirm the spill statistics shrink or disappear.
A larger warehouse made the query faster but the cost went upMore compute hid an inefficient query, and every run now costs more.Go back to the scan and spill fixes in the query itself, then test whether a smaller warehouse still meets your duration target.
The fix worked once but the query is slow again laterThe first run was not representative, or load or data volume changed.Compare several runs in query history and check warehouse load at the time of each slow run.
You cannot find a statistic named in this guideInterface labels and profile wording differ between releases and views.Check the official Snowflake documentation for Query Profile and match the label shown in your interface.

Frequently asked questions

Where do I start when a Snowflake query is slow?

Start with query history and warehouse load. Find the slow run, note its duration and start time, and check whether the warehouse was busy. Then open the Query Profile.

What does the Query Profile show?

It shows a visual breakdown of query execution, so you can optimize specific stages and reduce unnecessary scans.

Is a disk spill always a reason to buy a bigger warehouse?

No. Spilling often follows from reading and holding more data than needed. Improve filtering and the query structure first, then test a larger warehouse and measure the result.

How do I tell queuing from slow execution?

Check queuing time and warehouse load. If the delay is queue time and the execution itself is normal, the problem is contention, not the query.

How do I know a fix worked?

Re-run the query, compare its Query Profile and duration with the earlier runs, and check query history over several runs.

From Grokem: https://www.grokem.com/data/data-platforms/data-warehouses/snowflake-slow-query-with-disk-spills-read-the-query-profile-and-fix-it

Finished this article?

How did this strike you?

Be the first to react – it is anonymous.

Community

Questions about this?

Ask in Questions and help and get help from other readers.

Ask a questionBrowse Questions and help

Comments

No comments yet. Share what worked for you, or ask a question.

Keep learning

Picked for this article

Related reading, discussions and learning paths on the same subject.

All in Data Warehouses

From the community

Learning paths

Learning pathIntermediate

SQL for data work

From removing duplicates and window functions to modelling for reporting, cohort analysis, indexes and query plans: the SQL a working analyst or engineer uses every week, each lesson with queries that were run.

8 lessonsabout 90 minStart

Learning pathIntermediate

Data pipelines you can trust

How data gets from source to report without surprises: the ETL and ELT choice, a first pipeline, cleaning, quality checks, safe re-runs and scheduling.

7 lessonsabout 75 minStart

Choose what we may use. You can change this at any time with “Privacy choices” at the foot of every page. Details are in the Cookie policy and the Privacy policy.

Strictly necessary

Remembers your theme, the lessons you marked as done and this choice. It stays on your device, tracks nothing and cannot be switched off.

Always on
What exactly is collected, and why

Google Analytics

Collects
The pages you view, your device and browser type, your language, how you reached the site and your approximate location (country and city, worked out from your IP address).
Why
To understand which pages and lessons are used, so we can improve them.
Stored
Two cookies, _ga and _ga_<id>, for up to 2 years. Google acts for us; the data is processed by Google, including outside your country.

Grokem’s own counts

Collects
The pages you open and how long you stay, how far you scroll, where you came from, your device type, browser, screen size, language and time zone. Your IP address is turned into a code that changes every day; it is never stored as it is.
Why
To see how long lessons are read and where readers leave. Not shared with anyone.
Stored
A random visitor ID, a visit ID and the time of your last page view in your browser’s storage (gr_vid, gr_sid, gr_last). No name or email is attached.