Working with Resource Monitors¶

Resource Monitor Properties¶

A resource monitor is a first-class object in Snowflake, consisting of the following properties.

Assignment of Resource Monitors¶

A single monitor can be set at the account level to control credit usage for all warehouses in your account.

In addition, a monitor can be assigned to one or more warehouses, thereby controlling the credit usage for each assigned warehouse. Note, however, that a warehouse can be assigned to only a single resource monitor below the account level.

The following diagram illustrates a scenario in which one resource monitor is set at the account level and individual warehouses are assigned to two other resource monitors:

Based on this diagram:

• The credit quota for the entire account is 5000 for the interval (month, week, etc.), as controlled by Resource Monitor 1; if this quota is reached within the interval, the actions defined for the resource monitor (Suspend, Suspend Immediate, etc. ) are enforced for all five warehouses.

• Warehouse 3 can consume a maximum of 1000 credits within the interval.

• Warehouse 4 and 5 can consume a maximum combined total of 2500 credits within the interval.

Note that the actual credits consumed by Warehouses 3, 4, and 5 may be less than their quotas if the quota for the account is reached first.

Warehouse Suspension and Resumption¶

The used credits for a resource monitor reflects the sum of credits consumed by all assigned warehouses within the specified interval, along with the cloud services used to support those warehouses during the same interval. If a monitor has a Suspend or Suspend Immediately action defined and its used credits reach the threshold for the action, any warehouses assigned to the monitor are suspended and cannot be resumed until one of the following conditions is met:

• The next interval, if any, starts, as dictated by the start date for the monitor.

• The credit quota for the monitor is increased.

• The credit threshold for the suspend action is increased.

• The warehouses are no longer assigned to the monitor.

• The monitor is dropped.

Resource Monitor Notifications¶

When a resource monitor reaches the threshold for an action, it generates one of the following notifications, based on the action performed:

• The assigned warehouses will be suspended after all running queries complete.

• All running queries in the assigned warehouses will be canceled and the warehouses suspended immediately.

• A threshold has been reached, but no action has been performed.

The notification is sent to all account administrators who have enabled receipt of notifications and non-administrator users in the notification list.

Creating a Resource Monitor with a Default Schedule¶

To create a resource monitor that uses the default schedule (i.e. starts monitoring immediately and resets on the first day of each calendar month):

Classic Console

1. Use the ACCOUNTADMIN system role.

2. Click on Account » Resource Monitors » Create Resource Monitor.

3. Enter a name and quota.

4. Specify whether the resource monitor will be used for monitoring your account or individual warehouses.

5. Enter a threshold for at least one of the suspend actions. You can also specify up to five notify actions that simply send alerts without suspending any warehouses.

SQL

In SQL, this task is performed in two steps:

1. Execute a CREATE RESOURCE MONITOR command, but do not specify any scheduling properties.

2. Execute an ALTER WAREHOUSE command to assign warehouses to the resource monitor or an ALTER ACCOUNT command to set the resource monitor for the account.

For example:

• To create a monitor that starts monitoring immediately, resets at the beginning of each month, and suspends the assigned warehouse when the used credits reach 100% of the credit quota:

  USE ROLE ACCOUNTADMIN;
  
  CREATE OR REPLACE RESOURCE MONITOR limit1 WITH CREDIT_QUOTA=1000
    TRIGGERS ON 100 PERCENT DO SUSPEND;
  
  ALTER WAREHOUSE wh1 SET RESOURCE_MONITOR = limit1;
  

  Copy

  The SUSPEND action waits for currently-executing queries to finish before suspending the warehouse. This means that a query started before the trigger fires executes to completion and the warehouse consumes credits even after the quota is reached.

• To create a monitor that is similar to the first example, but suspends at 90% and suspends immediately at 100% to prevent all warehouses in the account from consuming credits after the quota has been reached:

  USE ROLE ACCOUNTADMIN;
  
  CREATE OR REPLACE RESOURCE MONITOR limit1 WITH CREDIT_QUOTA=1000
    TRIGGERS ON 90 PERCENT DO SUSPEND
             ON 100 PERCENT DO SUSPEND_IMMEDIATE;
  
  ALTER WAREHOUSE wh1 SET RESOURCE_MONITOR = limit1;
  

  Copy

  In this example, a notification is generated and the assigned warehouses are suspended when 90% usage is reached, which prevents the warehouses from executing any new queries, but allows currently-executing queries to complete. If the assigned warehouses reach 100% usage, a notification is generated and the warehouses are suspended immediately, canceling all currently-executing queries.

• To create a monitor that is similar to the first example, but lets the assigned warehouse exceed the quota by 10% and also includes two notification actions to alert account administrators as the used credits reach the halfway and three-quarters points for the quota:

  USE ROLE ACCOUNTADMIN;
  
  CREATE OR REPLACE RESOURCE MONITOR limit1 WITH CREDIT_QUOTA=1000
     TRIGGERS ON 50 PERCENT DO NOTIFY
              ON 75 PERCENT DO NOTIFY
              ON 100 PERCENT DO SUSPEND
              ON 110 PERCENT DO SUSPEND_IMMEDIATE;
  
  ALTER WAREHOUSE wh1 SET RESOURCE_MONITOR = limit1;
  

  Copy

  In this example:

  • When 50% and 75% usage is reached, an alert notification is sent to all account administrators who have enabled notifications, but no other actions are performed.

  • When 100% usage is reached, the assigned warehouse is suspended.

  • If the warehouse is still running when 110% usage is reached, it is suspended immediately.

Creating a Resource Monitor with a Custom Schedule¶

To create a resource monitor that uses a schedule other than the default:

Classic Console

1. Use the ACCOUNTADMIN system role.

2. Click on Account » Resource Monitors » Create Resource Monitor.

3. Enter a name and quota.

4. Specify whether the resource monitor will be used for monitoring your account or individual warehouses.

5. In the Schedule field, click the Customize link and specify one or more of the following properties, as desired:

   • Reset used credits based on the specified frequency.

   • Start monitoring credit usage immediately or at a later date and time.

   • Suspend warehouses at the specified end date and time, regardless of whether the credit quota threshold has been reached.

6. Enter a threshold for at least one of the suspend actions. You can also specify up to five notify actions that simply send alerts without suspending any warehouses.

SQL

Execute a CREATE RESOURCE MONITOR command, with one or more of the following scheduling properties:

• FREQUENCY

• START_TIMESTAMP

• END_TIMESTAMP

For example:

• To create an account-level resource monitor that starts immediately (based on the current timestamp), resets monthly on the same day, has no end date or time, and suspends the assigned warehouse when the used credits reach 100% of the quota:

  USE ROLE ACCOUNTADMIN;
  
  CREATE OR REPLACE RESOURCE MONITOR limit1 WITH CREDIT_QUOTA=1000
      FREQUENCY = MONTHLY
      START_TIMESTAMP = IMMEDIATELY
      TRIGGERS ON 100 PERCENT DO SUSPEND;
  
  ALTER WAREHOUSE wh1 SET RESOURCE_MONITOR = limit1;
  

  Copy

• To create a resource monitor that starts at a specific date and time in the future, resets weekly on the same day, has no end date or time, and performs two different suspend actions at different thresholds on two assigned warehouses:

  USE ROLE ACCOUNTADMIN;
  
  CREATE OR REPLACE RESOURCE MONITOR limit1 WITH CREDIT_QUOTA=2000
      FREQUENCY = WEEKLY
      START_TIMESTAMP = '2019-03-04 00:00 PST'
      TRIGGERS ON 80 PERCENT DO SUSPEND
               ON 100 PERCENT DO SUSPEND_IMMEDIATE;
  
  ALTER WAREHOUSE wh1 SET RESOURCE_MONITOR = limit1;
  
  ALTER WAREHOUSE wh2 SET RESOURCE_MONITOR = limit1;
  

  Copy