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:
| Metric | What It Tells You | Where to Find It |
|---|---|---|
| Bytes scanned | Total data read from storage | Query Profile, QUERY_HISTORY |
| Partitions scanned / total | Pruning efficiency | Query Profile |
| Spillage (local) | Data exceeded warehouse memory, spilled to SSD | Query Profile |
| Spillage (remote) | Data exceeded local SSD, spilled to remote storage | Query Profile |
| Execution time | Wall-clock time for the query | QUERY_HISTORY |
| Compilation time | Time spent parsing and optimizing | Query Profile |
| Queuing time | Time waiting for warehouse resources | QUERY_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.