Skip to main content

Query Performance Tuning & Profiling

A slow query isn't just a performance problem — it's a cost problem. Every second a warehouse spends crunching through an inefficient query is a second of credits burned. In this lesson, we'll cover how to identify, diagnose, and fix slow queries using Snowflake's built-in profiling tools and optimization features.


Reading the Query Profile in Snowsight​

The Query Profile is your primary diagnostic tool. After running a query in Snowsight, click the Query Profile tab to see the execution plan as a visual DAG (directed acyclic graph). Each node represents an operator — things like TableScan, Filter, Aggregate, Join, and Sort.

For each operator node, pay attention to:

  • Percentage of total execution time spent on that step
  • Records produced vs. records consumed
  • Bytes scanned from storage
  • Spillage to local or remote disk (a red flag for undersized warehouses)

The profile also shows you pruning efficiency — how many micro-partitions were scanned vs. how many existed in the table. If you're scanning 95% of partitions on a filtered query, your filtering isn't helping much.


Key Performance Metrics​

When analyzing query performance, these are the numbers that matter:

MetricWhat It Tells YouWhere to Find It
Bytes scannedTotal data read from storageQuery Profile, QUERY_HISTORY
Partitions scanned / totalPruning efficiencyQuery Profile
Spillage (local)Data exceeded warehouse memory, spilled to SSDQuery Profile
Spillage (remote)Data exceeded local SSD, spilled to remote storageQuery Profile
Execution timeWall-clock time for the queryQUERY_HISTORY
Compilation timeTime spent parsing and optimizingQuery Profile
Queuing timeTime waiting for warehouse resourcesQUERY_HISTORY

Spillage is the metric that most often indicates you need a larger warehouse. Local spillage is tolerable in small amounts, but remote spillage means you're hitting serious memory pressure.


Common Performance Anti-Patterns​

These are the issues we see repeatedly in production workloads:

SELECT * on wide tables. If you only need 5 columns from a 200-column table, select those 5 columns. Snowflake's columnar storage means unused columns are still scanned and decompressed when you use SELECT *.

Cartesian joins. A missing or incorrect join condition can produce millions of rows from modest input tables. Always verify your join predicates.

Excessive scalar UDFs. User-defined functions that execute row-by-row introduce overhead. If a UDF runs on every row of a billion-row table, that's a billion function invocations. Prefer SQL expressions or vectorized UDTFs where possible.

ORDER BY without LIMIT. Sorting an entire result set when you only need the top N rows forces the engine to materialize and sort everything. Always pair ORDER BY with LIMIT when feasible.

Overly complex CTEs referenced multiple times. Snowflake may re-evaluate a CTE each time it's referenced. If a CTE is expensive and used more than once, consider materializing it into a temporary table.


Clustering Keys​

Snowflake automatically organizes data into micro-partitions as it's loaded. Over time, especially with incremental loads, this natural clustering can degrade. Clustering keys let you define how data should be physically organized.

-- Add a clustering key
ALTER TABLE orders CLUSTER BY (order_date, region);

-- Check clustering quality
SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(order_date, region)');

The clustering information function returns metrics like average_overlap and average_depth. Lower values indicate better clustering. A well-clustered table lets Snowflake prune more micro-partitions during filtered queries.

When to use clustering keys:

  • Tables larger than ~1 TB
  • Queries that consistently filter on the same columns
  • When partition pruning shows poor efficiency in the Query Profile

When NOT to:

  • Small tables (clustering overhead exceeds the benefit)
  • Tables where query patterns vary widely with no dominant filter columns
  • Auto-clustering incurs ongoing maintenance costs — it's not free

Search Optimization Service​

The Search Optimization Service (SOS) accelerates point-lookup queries — the kind where you're searching for specific values rather than scanning ranges.

-- Enable search optimization on a table
ALTER TABLE customers ADD SEARCH OPTIMIZATION;

-- Enable on specific columns only (reduces cost)
ALTER TABLE customers ADD SEARCH OPTIMIZATION
ON EQUALITY(customer_id, email);

-- Check optimization status
SHOW TABLES LIKE 'customers';
-- Look for SEARCH_OPTIMIZATION = ON

SOS works best for queries like WHERE customer_id = 'ABC123' or WHERE email = 'user@example.com'. It builds a secondary search access structure behind the scenes. Be aware that SOS has ongoing storage and compute costs for maintaining the access structures.


Query Acceleration Service​

Query Acceleration Service (QAS) offloads portions of a query to shared compute resources, speeding up queries that scan large amounts of data.

-- Enable on a warehouse with a scale factor limit
ALTER WAREHOUSE analytics_wh
SET ENABLE_QUERY_ACCELERATION = TRUE
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;

The scale factor controls how much additional compute can be allocated — a factor of 8 means up to 8x the warehouse's base compute can be borrowed. Not every query benefits from QAS; it's most effective for queries with large scans and selective filters.

-- Check which queries would benefit from QAS
SELECT query_id, eligible_query_acceleration_time
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
WHERE eligible_query_acceleration_time > 60
ORDER BY eligible_query_acceleration_time DESC
LIMIT 20;

Caching Layers​

Snowflake uses three caching layers, and understanding them helps explain why the same query can have wildly different performance on repeated runs.

Metadata cache: For queries that can be answered entirely from metadata (like SELECT COUNT(*) FROM table), Snowflake returns the result instantly without starting a warehouse.

Result cache: If the same query is run again and the underlying data hasn't changed, Snowflake returns the cached result. This is free — no warehouse credits consumed. Results are cached for 24 hours.

Warehouse local disk cache: Data recently scanned by a warehouse is cached on the warehouse's local SSD. Subsequent queries that scan the same data benefit from faster reads. This cache is lost when the warehouse suspends.

-- Check if a query used the result cache
SELECT query_id, query_text, bytes_scanned,
percentage_scanned_from_cache
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_id = '<your_query_id>';

Finding Slow Queries with QUERY_HISTORY​

Rather than waiting for users to complain, proactively hunt for expensive queries:

-- Top 20 most expensive queries in the last 7 days
SELECT query_id,
query_text,
user_name,
warehouse_name,
total_elapsed_time / 1000 AS elapsed_seconds,
bytes_scanned / POWER(1024, 3) AS gb_scanned,
partitions_scanned,
partitions_total,
bytes_spilled_to_local_storage / POWER(1024, 3) AS gb_spilled_local,
bytes_spilled_to_remote_storage / POWER(1024, 3) AS gb_spilled_remote
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
AND execution_status = 'SUCCESS'
AND total_elapsed_time > 0
ORDER BY total_elapsed_time DESC
LIMIT 20;

Look for queries with high spillage, poor pruning ratios (partitions_scanned close to partitions_total), or disproportionate bytes_scanned relative to the result size.


Example: Diagnosing and Fixing a Slow Query​

Suppose this query takes 12 minutes on a Large warehouse:

-- Slow version
SELECT o.order_id, o.order_date, c.customer_name, p.product_name,
SUM(ol.quantity * ol.unit_price) AS line_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_lines ol ON o.order_id = ol.order_id
JOIN products p ON ol.product_id = p.product_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY 1, 2, 3, 4
ORDER BY line_total DESC;

Step 1: Check the Query Profile. We see 98% of partitions scanned on the orders table despite the date filter, and 4 GB spilled to remote storage.

Step 2: Add a clustering key on the dominant filter column:

ALTER TABLE orders CLUSTER BY (order_date);

Step 3: After reclustering completes, rerun the query. Partitions scanned drops from 98% to 12%, and spillage disappears. Execution time drops to 45 seconds.

Step 4: For repeated execution, the result cache makes subsequent identical runs instantaneous.


Key Takeaways​

  • The Query Profile is your first stop for any slow query investigation.
  • Spillage to remote storage is a strong signal to try a larger warehouse.
  • Clustering keys pay off on large tables with consistent filter patterns.
  • Search Optimization and Query Acceleration each target different query shapes — use the eligibility views to decide.
  • Proactively mine QUERY_HISTORY for expensive queries rather than waiting for complaints.