-- ============================================================================
-- spill-diagnostics.sql
-- Find queries that spilled to local or remote storage, ranked by severity.
-- Source: Sqlism (sqlism.com) -- "Snowflake Query Spilling Explained: Local
-- vs Remote Spill"
--
-- Requires: ACCOUNTADMIN, or a role granted IMPORTED PRIVILEGES on the
-- SNOWFLAKE database (needed to query ACCOUNT_USAGE views).
-- ============================================================================

-- 1) Worst offenders in the last 7 days, ranked by remote spill first
--    (remote spill is the one that actually hurts -- see the article for why).
SELECT
    query_id,
    warehouse_name,
    warehouse_size,
    LEFT(query_text, 120)              AS query_text_preview,
    bytes_spilled_to_local_storage,
    bytes_spilled_to_remote_storage,
    ROUND(total_elapsed_time / 1000.0, 1) AS total_elapsed_seconds,
    start_time
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  AND (bytes_spilled_to_local_storage > 0 OR bytes_spilled_to_remote_storage > 0)
ORDER BY bytes_spilled_to_remote_storage DESC, bytes_spilled_to_local_storage DESC
LIMIT 50;

-- 2) Which warehouses spill chronically? A warehouse that shows up here
--    every day is a right-sizing candidate, not a one-off query to tune.
SELECT
    warehouse_name,
    warehouse_size,
    COUNT(*)                                        AS spilling_queries,
    SUM(bytes_spilled_to_local_storage)              AS total_local_spill_bytes,
    SUM(bytes_spilled_to_remote_storage)             AS total_remote_spill_bytes,
    ROUND(AVG(total_elapsed_time) / 1000.0, 1)       AS avg_elapsed_seconds
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
  AND (bytes_spilled_to_local_storage > 0 OR bytes_spilled_to_remote_storage > 0)
GROUP BY warehouse_name, warehouse_size
ORDER BY total_remote_spill_bytes DESC, total_local_spill_bytes DESC;

-- 3) Note on Query Acceleration Service (QAS):
--    If QAS is enabled on a warehouse, Snowflake writes a small amount of
--    data to remote storage for every eligible query even when QAS isn't
--    actually invoked -- so a small, steady bytes_spilled_to_remote_storage
--    value across many queries on a QAS-enabled warehouse is not automatically
--    a performance problem. Look for spill volumes that are large relative to
--    bytes_scanned, and for total_elapsed_time that moves with the spill size,
--    before concluding a query needs attention.
SELECT
    query_id,
    warehouse_name,
    bytes_scanned,
    bytes_spilled_to_remote_storage,
    ROUND(bytes_spilled_to_remote_storage / NULLIF(bytes_scanned, 0), 3) AS remote_spill_to_scan_ratio,
    ROUND(total_elapsed_time / 1000.0, 1) AS total_elapsed_seconds
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  AND bytes_spilled_to_remote_storage > 0
ORDER BY remote_spill_to_scan_ratio DESC
LIMIT 50;