Sev1: Snowflake warehouses suspended by a resource monitor — dashboards and loads stop

#Symptoms

• Queries fail with messages that the warehouse is suspended due to a resource monitor or quota.
• BI dashboards, ELT jobs and apps fail together across one or more warehouses.
• Credit usage alert fired earlier, or nobody noticed it.

#1. Confirm it is the resource monitor

SHOW RESOURCE MONITORS;
SHOW WAREHOUSES;

In SHOW RESOURCE MONITORS check credit_quota, used_credits, remaining_credits, and which level (account or warehouse). In SHOW WAREHOUSES, check state and resource_monitor.

#2. Find what burned the credits

Before raising the quota, find out whether something is still running away:

SELECT warehouse_name, SUM(credits_used) AS credits
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD(day, -2, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY 2 DESC;

SELECT query_id, user_name, warehouse_name, warehouse_size,
       total_elapsed_time/1000 AS secs, LEFT(query_text, 120) AS q
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
ORDER BY total_elapsed_time DESC
LIMIT 20;

ACCOUNT_USAGE views have latency (up to a few hours). For the last minutes use INFORMATION_SCHEMA.QUERY_HISTORY() table function.

Common culprits: a warehouse resized to 4X-Large and left there, AUTO_SUSPEND set to never, a runaway task or a looping BI refresh.

#3. Restore service

Get approval for the extra spend, then raise the quota (requires ACCOUNTADMIN or the monitor's owner with privileges):

USE ROLE ACCOUNTADMIN;
ALTER RESOURCE MONITOR rm_prod SET CREDIT_QUOTA = 6000;

Resume warehouses if they don't resume on the next query:

ALTER WAREHOUSE wh_bi RESUME IF SUSPENDED;
ALTER WAREHOUSE wh_elt RESUME IF SUSPENDED;

Warning

Removing the monitor from the warehouse (ALTER WAREHOUSE ... SET RESOURCE_MONITOR = NULL) restores service but also removes the protection. If the runaway workload is still running you will just burn the rest of the budget. Fix the cause first or raise the quota by a controlled amount.

#4. Stop the runaway

-- Cancel a specific query
SELECT SYSTEM$CANCEL_QUERY('<query_id>');
-- Suspend a misbehaving task
ALTER TASK analytics.public.hourly_rebuild SUSPEND;
-- Right-size and set auto-suspend
ALTER WAREHOUSE wh_adhoc SET WAREHOUSE_SIZE = 'SMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;

#5. Protect critical workloads

Give critical pipelines their own warehouse with its own monitor, so an ad-hoc spike can't stop production loads:

CREATE RESOURCE MONITOR rm_elt WITH CREDIT_QUOTA = 2000 FREQUENCY = MONTHLY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 75 PERCENT DO NOTIFY
           ON 90 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE wh_elt SET RESOURCE_MONITOR = rm_elt;

#Prevent it next time

• Notify triggers at 50%, 75% and 90% with named owners who act on them.
• STATEMENT_TIMEOUT_IN_SECONDS on ad-hoc warehouses to kill runaway queries.
• Separate warehouses (and monitors) for BI, ELT and ad-hoc.
• Weekly review of the top credit-consuming queries and warehouses.