Snowflake vs Redshift

Identifies queries suffering from excessive WLM queue wait times and disk spills to pinpoint undersized service classes.

-- Non-blocking telemetry: identify queries with high queue time and disk spills
SELECT
  q.query,
  s.service_class AS wlm_queue,
  ROUND(q.queue_time / 1000000.0, 2) AS queue_sec,
  ROUND(q.exec_time / 1000000.0, 2) AS exec_sec,
  m.is_diskbased AS disk_spill,
  SUBSTRING(q.querytxt, 1, 60) AS query_snippet
FROM stl_wlm_query q
JOIN stv_wlm_service_class_config s ON q.service_class = s.service_class
LEFT JOIN svl_query_summary m ON q.query = m.query AND m.is_diskbased = 't'
WHERE q.exec_time > 10000000 -- queries > 10s
ORDER BY q.queue_time DESC
LIMIT 10;

Audits virtual warehouses experiencing queue overload and tracks automatic clustering credit burn across large tables over the preceding 7 days.

-- Identify virtual warehouses experiencing queue overload
SELECT
  warehouse_name,
  ROUND(AVG(avg_running), 2) AS avg_running_clusters,
  ROUND(AVG(avg_queued_load), 2) AS avg_queued_queries,
  ROUND(AVG(avg_blocked), 2) AS avg_blocked_queries
FROM snowflake.account_usage.warehouse_load_history
WHERE start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
HAVING avg_queued_queries > 0.5
ORDER BY avg_queued_queries DESC;

-- Audit clustering credit consumption by table
SELECT
  table_name,
  database_name,
  schema_name,
  ROUND(SUM(credits_used), 2) AS total_clustering_credits
FROM snowflake.account_usage.automatic_clustering_history
WHERE start_time >= DATEADD(days, -7, CURRENT_TIMESTAMP())
GROUP BY table_name, database_name, schema_name
ORDER BY total_clustering_credits DESC
LIMIT 5;