Query Profile Analysis: Find Your Most Expensive Queries in Minutes

Query Profile Analysis: Find Your Most Expensive Queries in Minutes

The QUERY_HISTORY view reveals which queries consume the most credits. Learn how to identify and optimize your top cost drivers.

The 80/20 Rule

Typically, 20% of queries consume 80% of compute resources. Finding and fixing these queries is the fastest path to cost reduction.

Finding Your Most Expensive Queries

SELECT
QUERY_TEXT,
USER_NAME,
WAREHOUSE_NAME,
COUNT(*) as EXECUTION_COUNT,
AVG(TOTAL_ELAPSED_TIME)/1000 as AVG_DURATION_SECONDS,
SUM(TOTAL_ELAPSED_TIME)/1000/3600 as TOTAL_HOURS,
(TOTAL_HOURS *
CASE WAREHOUSE_SIZE
WHEN 'X-SMALL' THEN 1
WHEN 'SMALL' THEN 2
WHEN 'MEDIUM' THEN 4
WHEN 'LARGE' THEN 8
END) as APPROX_CREDITS
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3, WAREHOUSE_SIZE
ORDER BY APPROX_CREDITS DESC
LIMIT 20;

Common Expensive Query Patterns

1. SELECT * on Large Tables

-- EXPENSIVE (scans 500GB)
SELECT * FROM fact_transactions
WHERE transaction_date = CURRENT_DATE();

-- OPTIMIZED (scans 5GB)
SELECT transaction_id, amount, customer_id
FROM fact_transactions
WHERE transaction_date = CURRENT_DATE();

2. Missing JOIN Conditions

-- EXPENSIVE (Cartesian product)
SELECT o.*, c.*
FROM orders o, customers c;

-- OPTIMIZED
SELECT o.*, c.*
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Real-World Example

A company's reporting dashboard ran a query 500 times/day scanning 2TB per query × 500 = 1PB/day, costing ~1,000 credits/day.

After optimization (specific columns only, filtered to 3 months): Scan 50GB per query × 500 = 25TB/day, cost ~25 credits/day. Savings: 975 credits/day = $87,750/month.

Key Takeaways

• 20% of queries typically consume 80% of compute
• Use QUERY_HISTORY to find expensive queries
• Look for: SELECT *, missing filters, inefficient JOINs
• Optimize highest-impact queries first
• Automate monitoring to catch new expensive queries