Cache Hit Ratio Optimization

infoProactive Health

Poor cache utilization causing repeated expensive data scans and query compilation overhead when cached results could be reused.

Prompt: “I'm seeing the same Snowflake queries run multiple times per day but they're always scanning from remote storage instead of using cached results. Help me understand why the result cache isn't being used and how to optimize warehouse cache retention.”

Agent Playbook

When an agent encounters this scenario, Schema provides these diagnostic steps automatically.

When investigating Snowflake cache hit ratio issues, start by confirming result cache is enabled, then measure your actual cache hit rates to quantify the problem. Focus on identifying query text variations that prevent cache reuse—even whitespace differences break the cache—then analyze whether underlying data changes are invalidating results prematurely.

1Verify result cache is enabled at session and account level

The first thing to check is whether USE_CACHED_RESULT is actually enabled. Run `SHOW PARAMETERS LIKE 'USE_CACHED_RESULT'` at both session and account level—applications or BI tools may be explicitly disabling it without you realizing. If disabled, enable it with `ALTER SESSION SET USE_CACHED_RESULT = TRUE` to allow Snowflake to return cached results for identical queries within the 24-hour cache window. This is the most common oversight and the easiest fix.

Result cache disabled degrades query performance

2Measure your actual cache hit rate and identify high-cost repeat queries

Check `snowflake-query-data-scanned-cache-avg` to see what percentage of data is being served from cache versus remote storage—if this is consistently low (<30%), you have a real problem. Cross-reference with `snowflake-billing-warehouse-credits-used` to quantify the cost impact of cache misses. Look for patterns where the same expensive queries run multiple times per day but show zero cache hits—these are your biggest credit-wasting offenders and should be your optimization targets.

Result cache underutilization increases duplicate compute Result Cache provides zero-cost query responses for repeated queries snowflake_query_data_scanned_cache_avgsnowflake_billing_warehouse_credits_used

3Query QUERY_HISTORY to find identical queries with different text formatting

Use `SELECT QUERY_TEXT, COUNT(*), SUM(TOTAL_ELAPSED_TIME) FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY GROUP BY QUERY_TEXT` to identify queries that appear similar but have minor text variations preventing cache hits. Even a single extra space or different capitalization breaks the cache. Focus on queries with high `snowflake-query-executed` counts and elevated `snowflake-query-compilation-time-avg`—these are being recompiled and re-executed when they should be returning instant cached results.

Repeated expensive queries waste credits when result cache is not leveraged Query result cache not leveraged for 24-hour reuse window snowflake_query_executedsnowflake_query_compilation_time_avg

4Analyze application query generation patterns for consistency

Review your application code, BI dashboards, and ETL scripts to see if they're generating dynamic SQL with varying text instead of using parameterized queries or consistent formatting. Common culprits include timestamp literals embedded in WHERE clauses, random ordering of columns or JOINs, or different comment blocks. Refactor to use identical query text wherever possible—this is especially critical for BI dashboards where multiple users view the same report and should all benefit from a single cache entry.

Query result cache not leveraged for 24-hour reuse window Result Cache provides zero-cost query responses for repeated queries snowflake_count_elapsed_time_avg

5Check if frequent data updates are invalidating the cache prematurely

The result cache is automatically invalidated when underlying tables change, so frequent micro-batch updates can prevent cache hits even with identical queries. Query SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS or QUERY_HISTORY filtered by query type (INSERT/UPDATE/DELETE/MERGE) to see if your tables are being modified more often than you realize. If data changes every few minutes but your queries run every hour, consider batching updates or using separate reporting tables that refresh less frequently to maximize the 24-hour cache window.

Repeated expensive queries waste credits when result cache is not leveraged Result cache underutilization increases duplicate compute snowflake_query_executed