snowflake_query_data_scanned_cache_avg

Average percentage of data scanned from cache over the last 24 hour window.

Dimensions:None

Technical Annotations (21)

Configuration Parameters (2)

USE_CACHED_RESULTrecommended: TRUE

Enables result caching for identical queries (default ON)

auto_suspend_timeoutrecommended: 0 for ETL, 600 for BI, 300 for DevOps

Suspension timeout in seconds for warehouse workload type

CLI Commands (2)

ALTER SESSION SET USE_CACHED_RESULT = TRUE;remediation

SET warehouse_names = 'COMPUTE_WH, DEV_WH';
SELECT
    CASE
        WHEN bytes_scanned / power(1024,3) < 0.1 THEN 1
        WHEN bytes_scanned / power(1024,3) < 1 THEN 2
        WHEN bytes_scanned / power(1024,3) < 10 THEN 3
        WHEN bytes_scanned / power(1024,3) < 20 THEN 4
        WHEN bytes_scanned / power(1024,3) < 50 THEN 5
        WHEN bytes_scanned / power(1024,3) < 100 THEN 6
        WHEN bytes_scanned / power(1024,3) < 200 THEN 7
        WHEN bytes_scanned / power(1024,3) < 500 THEN 8
        WHEN bytes_scanned / power(1024,3) < 1000 THEN 9
        ELSE 10 END as gb_ranking
    , CASE
        WHEN bytes_scanned / power(1024,3) < 0.1 THEN '0 to 100MB'
        WHEN bytes_scanned / power(1024,3) < 1 THEN '100MB to 1GB'
        WHEN bytes_scanned / power(1024,3) < 10 THEN '1GB to 10GB'
        WHEN bytes_scanned / power(1024,3) < 20 THEN '10GB to 20GB'
        WHEN bytes_scanned / power(1024,3) < 50 THEN '20GB to 50GB'
        WHEN bytes_scanned / power(1024,3) < 100 THEN '50GB to 100GB'
        WHEN bytes_scanned / power(1024,3) < 200 THEN '100GB to 200GB'
        WHEN bytes_scanned / power(1024,3) < 500 THEN '200GB to 500GB'
        WHEN bytes_scanned / power(1024,3) < 1000 THEN '500GB to 1TB'
        ELSE '1TB+' END as gb_scanned
    , COUNT(*) as num_queries
    , AVG(execution_time)*0.001 as avg_execution_time_s
    , PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY execution_time ASC)*0.001 as p95
FROM snowflake.account_usage.query_history AS qh
WHERE
    1=1
    AND qh.warehouse_name IN (SELECT TRIM(VALUE) FROM TABLE(SPLIT_TO_TABLE($warehouse_names, ',')))
    AND qh.warehouse_size IS NOT NULL
    AND qh.start_time >= current_date - 90
    AND qh.query_type = 'SELECT'
GROUP BY 1,2
ORDER BY 1 ASC;

diagnostic

Technical References (17)

snowflake.account_usage.query_historycomponentpartitions_scannedconceptCLUSTER BYconceptpredicate pushdownconceptQUERY_HISTORYcomponentresult cachecomponentResult Cachecomponentglobal result cachecomponentWarehouse CachecomponentLocal Disk CachecomponentSSDcomponentmulti-cluster warehousescomponentwarehouse cacheconceptbytes_scannedconceptdata cachesconceptETL/ELT warehousescomponentBI warehousescomponent

Related Insights (11)

Slow queries exceeding 60 seconds indicate performance bottleneckswarning

▸

Result cache disabled degrades query performanceinfo

▸

Full table scans without cluster key predicates waste compute creditsinfo

▸

SELECT * queries on large datasets consume excessive creditswarning

▸

Query result cache not leveraged for 24-hour reuse windowinfo

▸

Result Cache provides zero-cost query responses for repeated queriesinfo

▸

Warehouse local disk cache reduces remote storage accessinfo

▸

Multiple warehouses per team multiply idle billing windows and scatter cachewarning

▸

Majority of queries scan under 100 GB indicating engine mismatchinfo

▸

Inappropriate warehouse timeout settings degrade cache performance or waste creditsinfo

▸

Result cache underutilization increases duplicate computeinfo

▸