Virtual Warehouse Optimization: Right-Size for Peak Efficiency

Virtual Warehouse Optimization: Right-Size for Peak Efficiency

Analyze virtual warehouse performance metrics and adjust sizing to eliminate over-provisioning while maintaining optimal query performance.

Performance vs Cost Trade-off

Larger warehouses provide more compute power but cost scales linearly. The key is finding the minimum size that meets performance requirements.

Warehouse Sizing Guide

• X-Small: Development, testing, simple queries (<10s execution)
• Small: BI dashboards, moderate workloads (10-30s execution)
• Medium: Standard analytics, ETL (30-60s acceptable)
• Large: Complex analytics, large aggregations (1-5 min)
• X-Large+: Massive datasets, time-critical processing only

Performance Analysis Query

SELECT
WAREHOUSE_NAME,
WAREHOUSE_SIZE,
COUNT(*) as QUERY_COUNT,
AVG(EXECUTION_TIME)/1000 as AVG_SECONDS,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY EXECUTION_TIME)/1000 as P95_SECONDS
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD(day, -7, CURRENT_TIMESTAMP())
AND WAREHOUSE_NAME IS NOT NULL
GROUP BY 1, 2
ORDER BY 1;

Decision Framework

• If P95 < 10s: Consider downsizing
• If P95 > 60s: Consider upsizing or query optimization
• If P95 10-60s: Current size likely appropriate

Real-World Example

A company used Large warehouses for all workloads. Analysis showed 70% of queries completed in <15 seconds (didn't need Large). By creating separate warehouses: BI queries → Small warehouse (70% of workload), complex analytics → Medium (25% of workload), massive aggregations → Large (5% of workload). Overall cost reduction: 55%.

Key Takeaways

• Right-size based on actual query performance metrics
• Use smallest warehouse that meets performance SLAs
• Separate workload types into appropriately-sized warehouses
• Monitor P95 execution times, not just averages
• Query optimization often better than warehouse upsizing