Resource Monitor: Helps control costs and avoid unexpected credit usage caused by running warehouses, The number of credits consumed depends on the size of the warehouse and how long it runs. Limits can be set for a specified interval or date range. When these limits are reached and/or are approaching, the resource monitor can trigger various actions, such as sending alert notifications and/or suspending the warehouses.
Resource monitors support the following actions:
Notify & Suspend
Send a notification (to all account administrators with notifications enable) and suspend all assigned warehouses after all statements being execute by the warehouse(s) have completed.
Notify & Suspend Immediately
Send a notification (to all account administrators with notifications enable) and suspend all assigned warehouses immediately, which cancels any statements being execute by the warehouses at the time.
Perform no action, but send an alert notification (to all account administrators with notifications enabled).A resource monitor must have at least one action defined; if no actions have been define, nothing happens when the used credits reach the threshold.
Without going much into theory part, we will see how to setup the Resource monitor.
Lets say you executed below query without join and it will be eating up the CREDIT Quota assigned to Warehouse.
SELECT * FROM INVOICE,INVOICE1;
We will see the notifications, once the threshold reaches
Once the threshold reaches, warehouse gets suspend and all the running statements get terminates.
So if you try to run the new query, you will get the below message
In order to resume warehouse, either drop the RESOURCE MONITOR or increase the credit quota in below way.
Query to create WH:
CREATE RESOURCE MONITOR "MONITOR_QUOTA" WITH CREDIT_QUOTA = 2
ON 60 PERCENT DO SUSPEND
ON 75 PERCENT DO SUSPEND_IMMEDIATE
ON 40 PERCENT DO NOTIFY;
ALTER WAREHOUSE "COMPUTE_WH" SET RESOURCE_MONITOR = "MONITOR_QUOTA";