Query Profiling and Performance Monitoring¶

SELECT query_id, query_text, user_name, warehouse_name,
       execution_time/1000 AS exec_seconds,
       bytes_scanned/1024/1024/1024 AS gb_scanned,
       partitions_scanned, partitions_total,
       ROUND(partitions_scanned/NULLIF(partitions_total, 0) * 100, 2) AS pct_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP())
  AND execution_time > 60000
  AND query_type IN ('SELECT', 'INSERT', 'CREATE_TABLE_AS_SELECT')
ORDER BY execution_time DESC
LIMIT 20;

SELECT user_name,
       COUNT(*) AS query_count,
       AVG(execution_time)/1000 AS avg_seconds,
       SUM(bytes_scanned)/1024/1024/1024 AS total_gb_scanned,
       SUM(credits_used_cloud_services) AS cloud_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY user_name
ORDER BY total_gb_scanned DESC
LIMIT 20;

SELECT error_code, error_message,
       COUNT(*) AS failure_count,
       ARRAY_AGG(DISTINCT user_name) AS affected_users
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  AND execution_status = 'FAIL'
GROUP BY error_code, error_message
ORDER BY failure_count DESC;

-- Disable result cache for a session (testing purposes)
ALTER SESSION SET USE_CACHED_RESULT = FALSE;

-- Check if query used result cache
SELECT query_id, query_text, execution_status,
       CASE WHEN bytes_scanned = 0 AND rows_produced > 0
            THEN 'RESULT_CACHE_HIT' ELSE 'COMPUTED' END AS cache_status
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('hour', -1, CURRENT_TIMESTAMP())
ORDER BY start_time DESC;

-- Check clustering information
SELECT SYSTEM$CLUSTERING_INFORMATION('my_db.public.large_table', '(date_col, region_col)');

-- Clustering history and cost
SELECT table_name, SUM(credits_used) AS clustering_credits,
       SUM(num_rows_reclustered) AS rows_reclustered
FROM SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY table_name
ORDER BY clustering_credits DESC;

-- Add clustering key
ALTER TABLE large_facts CLUSTER BY (sale_date, region);

-- Change clustering key
ALTER TABLE large_facts CLUSTER BY (sale_date, product_category);

-- Remove clustering key
ALTER TABLE large_facts DROP CLUSTERING KEY;

-- Suspend reclustering (save credits while investigating)
ALTER TABLE large_facts SUSPEND RECLUSTER;

-- Resume reclustering
ALTER TABLE large_facts RESUME RECLUSTER;

-- Daily health check summary
SELECT 'Active Queries' AS metric, COUNT(*) AS value
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_WAREHOUSE())
WHERE execution_status = 'RUNNING'
UNION ALL
SELECT 'Failed Queries (24h)', COUNT(*)
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE execution_status = 'FAIL'
  AND start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP())
UNION ALL
SELECT 'Failed Logins (24h)', COUNT(*)
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE is_success = 'NO'
  AND event_timestamp >= DATEADD('day', -1, CURRENT_TIMESTAMP())
UNION ALL
SELECT 'Credits Used (today)', SUM(credits_used)::VARCHAR
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATE_TRUNC('day', CURRENT_TIMESTAMP());