How to Diagnose Slow Queries in Snowflake

Galaxy Glossary

How do I identify and speed up slow queries in Snowflake?

Use Snowflake’s system views, query profile, and warehouse tuning to detect and fix slow-running SQL.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What makes a Snowflake query slow?

Large scans, skewed joins, insufficient warehouse size, and missing micro-partition pruning top the list. Identifying the exact cause requires inspecting execution history and profiles.

How can I list my slowest queries?

Run SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY or INFORMATION_SCHEMA.QUERY_HISTORY filtering on long EXECUTION_TIME. Sort descending to surface the worst offenders.

Example

SELECT query_id, user_name, execution_time/1000 AS secs, start_time, query_textFROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORYWHERE start_time >= DATEADD(day,-7,CURRENT_TIMESTAMP()) AND execution_time > 30000 -- >30sORDER BY execution_time DESC;

How do I inspect a specific query’s plan?

Click the query_id in Snowflake UI or run SELECT SYSTEM$GET_QUERY_PROFILE('query_id');. Focus on steps with high percentages in "Elapsed" or "Partitions scanned".

Which SQL patterns slow ecommerce analytics?

Unrestricted SELECT *, joining Orders to OrderItems without filtering dates, and using functions on created_at prevent pruning and create giant scans.

How to rewrite a slow join?

Filter early, select needed columns, and cast predicates to match storage types. Replace:

SELECT * FROM Orders oJOIN OrderItems oi ON oi.order_id=o.idWHERE DATE(o.order_date)='2024-01-01';

with:

SELECT o.id, o.total_amount, SUM(oi.quantity) qtyFROM Orders oJOIN OrderItems oi USING (id)WHERE o.order_date = '2024-01-01'GROUP BY o.id,o.total_amount;

How to tune warehouses for faster runs?

Scale up (larger X-L) for CPU-bound queries; scale out (multi-cluster) for concurrency; suspend when idle to save cost. Use AUTO_SUSPEND=60, AUTO_RESUME=TRUE.

Can I abort runaway queries automatically?

Yes. Set ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 30; or apply a resource monitor with EXECUTION_TIME_LIMIT_IN_SECONDS.

Best practices summary

1) Always filter on partition columns.
2) Avoid functions on columns in predicates.
3) Inspect QUERY_HISTORY weekly.
4) Keep warehouses right-sized.
5) Prefer clustering for high-value tables.

Why How to Diagnose Slow Queries in Snowflake is important

How to Diagnose Slow Queries in Snowflake Example Usage


-- Find top 5 slowest customer queries last week
SELECT query_id, execution_time/1000 AS secs, query_text
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE user_name = CURRENT_USER()
  AND start_time >= DATEADD(day,-7,CURRENT_TIMESTAMP())
ORDER BY execution_time DESC
LIMIT 5;

How to Diagnose Slow Queries in Snowflake Syntax


-- List slow queries
SELECT query_id, user_name, execution_time/1000 AS secs,
       query_text
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE execution_time > 30000 -- ms
ORDER BY execution_time DESC;

-- Abort after 30s per session
ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 30;

-- Scale warehouse before heavy Orders analysis
ALTER WAREHOUSE ANALYTICS_WH SET WAREHOUSE_SIZE = 'LARGE';

-- Rewrite slow ecommerce join
SELECT c.id, c.name, SUM(oi.quantity) AS total_items
FROM Customers c
JOIN Orders o   ON o.customer_id = c.id
JOIN OrderItems oi ON oi.order_id = o.id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.id, c.name;

Common Mistakes

Relying solely on warehouse scaling. Bigger warehouses hide inefficiencies but inflate cost. Always inspect the query profile and rewrite SQL first.
Using functions on partition columns (e.g., DATE(order_date)). This disables micro-partition pruning. Compare raw columns directly or pre-compute needed fields.