Managing Resource Monitors¶
A virtual warehouse consumes Snowflake credits for each hour that it runs. The number of credits consumed depends on the size of the warehouse and how long it runs.
To help control costs and avoid unexpected credit usage related to using warehouses, Snowflake provides resource monitors. Resource Monitors can be used by account administrators to impose limits on the number of credits that warehouses can consume within each monthly billing period.
In this Topic:
Resource Monitor Properties¶
A resource monitor is a first-class object in Snowflake, consisting of the following properties:
The number of Snowflake credits allocated to the monitor for each monthly billing period.
The total accumulated number of Snowflake credits used during the current monthly billing period by all warehouses assigned the monitor. At the beginning of each monthly billing cycle, this number resets to 0.
A trigger specifies a credit quota threshold (as a percentage; values greater than 100 are allowed) and an action to perform when the threshold is reached. If credit usage reaches the threshold for the monthly billing period, the trigger fires and the action is performed. The following actions are supported:
A resource monitor can have multiple triggers, but must have at least one trigger to perform any actions (i.e. if a monitor does not have any triggers defined, nothing happens when the credit usage reaches the credit quota for the month).
Resource Monitor Assignment¶
A monitor can be assigned to one or more warehouses, thereby controlling the credit usage for each warehouse assigned to it; however, each warehouse can be assigned only a single monitor. In addition, a monitor can also be set at the account level to control credit usage for all warehouses in your account.
An account-level resource monitor does not override a resource monitor assigned to a warehouse. If either resource monitor reaches its defined threshold, the warehouse is suspended. This enables controlling global credit usage while also providing fine-grained control over credit usage for individual/specific warehouses.
The following diagram illustrates a resource monitor set at the account level, as well as resource monitors assigned to individual warehouses:
Based on this diagram:
- The credit quota for the entire account is 5000 for the month (dictated by Resource Monitor 1); if this quota is reached, the actions defined for the resource monitor (e.g. warehouse suspension) are enforced.
- Warehouse 3 and 4 can consume a maximum combined total of 2500 credits for the month.
- Warehouse 5 can consume a maximum total of 1000 credits for the month.
- The actual credits consumed by Warehouses 4, 5, and 6 may be less than their quotas if the quota for the account is reached first.
Warehouse Suspension and Resumption¶
The credit usage for a resource monitor reflects the sum of all credits used by all warehouses assigned the monitor during the current billing period. If a monitor’s usage reaches its quota for the month and a suspend action has been triggered for the monitor, any warehouses associated with the monitor are suspended and cannot be resumed until one of the following conditions is met:
- A new monthly billing cycle starts.
- The credit quota for the monitor is increased.
- The credit threshold for the monitor trigger is increased (if the threshold percentage was less than 100).
- The monitor is no longer assigned to the warehouse.
- The monitor is dropped.
Resource Monitor DDL¶
Snowflake provides the following DDL commands for creating and using/managing resource monitors:
In addition, the following commands can be used to assign a resource monitor to a warehouse and view whether a warehouse has an assigned monitor:
Creating Resource Monitors¶
Only account administrators (i.e. users with the ACCOUNTADMIN role) can create resource monitors.
Use the CREATE RESOURCE MONITOR command to create a resource monitor:
This first example creates a monitor named
limit1that suspends all associated warehouses when the quota of 1000 credits is reached for the month:
USE ROLE ACCOUNTADMIN; CREATE OR REPLACE RESOURCE MONITOR limit1 WITH CREDIT_QUOTA=1000 TRIGGERS ON 100 PERCENT DO SUSPEND;
The SUSPEND action waits for currently-executing queries to finish before suspending the warehouse. This means that a query started before the trigger was activated continues to use credits even after the quota is reached.
To avoid warehouses using credits after the quota has been reached, use SUSPEND_IMMEDIATE. For example:
USE ROLE ACCOUNTADMIN; CREATE OR REPLACE RESOURCE MONITOR limit1 WITH CREDIT_QUOTA=1000 TRIGGERS ON 100 PERCENT DO SUSPEND ON 110 PERCENT DO SUSPEND_IMMEDIATE;
In this example, the associated warehouses are suspended when 100% usage is reached, which prevents the warehouses from executing any new queries, but allows currently-executing queries to complete. If the currently-executing queries reach 110% usage, the warehouses are suspended immediately, canceling the queries.
Assigning a Resource Monitor to a Warehouse¶
The following example uses the warehouse (
mywh) and resource monitor (
limit1) created in the previous example:
ALTER WAREHOUSE mywh SET RESOURCE_MONITOR = limit1;
Modifying a Resource Monitor¶
Use the ALTER RESOURCE MONITOR command to modify an existing monitor. Modification of a resource monitor is limited to the role that owns the monitor (typically the ACCOUNTADMIN role). You can modify the monthly credit quota for a monitor, as well as replace the triggers for the monitor.
The following example changes the monthly credit quota for
ALTER RESOURCE MONITOR limit1 SET CREDIT_QUOTA=2000;
Viewing Resource Monitors¶
To view all the resource monitors defined for your account, use the SHOW RESOURCE MONITORS command.
To view the warehouses in your account and the resource monitor, if any, associated with each warehouse, use the SHOW WAREHOUSES command.
Defining a Global Resource Monitor for Your Account¶
Global monthly credit usage for an account can be controlled by setting a resource monitor at the account level. When the combined credit usage for all warehouses in the account reaches the defined threshold, all the warehouses are suspended.
CREATE RESOURCE MONITOR accountmax WITH CREDIT_QUOTA=10000 TRIGGERS ON 100 PERCENT DO SUSPEND; ALTER ACCOUNT SET RESOURCE_MONITOR = accountmax;