Categories:

Warehouse & Resource Monitor DDL

CREATE RESOURCE MONITOR

Creates a new resource monitor. This command can only be executed by account administrators. For more information about resource monitors, see Working with Resource Monitors.

See also:

ALTER RESOURCE MONITOR , DROP RESOURCE MONITOR , SHOW RESOURCE MONITORS , ALTER WAREHOUSE , ALTER ACCOUNT

Syntax

CREATE [ OR REPLACE ] RESOURCE MONITOR <name> WITH
                      [ CREDIT_QUOTA = <number> ]
                      [ FREQUENCY = { MONTHLY | DAILY | WEEKLY | YEARLY | NEVER } ]
                      [ START_TIMESTAMP = { <timestamp> | IMMEDIATELY } ]
                      [ END_TIMESTAMP = <timestamp> ]
                      [ TRIGGERS triggerDefinition [ triggerDefinition ... ] ]

Where:

triggerDefinition ::=
    ON <threshold> PERCENT DO { SUSPEND | SUSPEND_IMMEDIATE | NOTIFY }

Required Parameters

name

Identifier for the resource monitor; must be unique for your account.

The identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Syntax.

Optional Parameters

CREDIT_QUOTA = num

The number of credits allocated monthly to the resource monitor. When total usage for all warehouses assigned to the monitor reaches this number for the current month (i.e. billing period), the resource monitor is considered to be at 100% of quota.

If a value is not specified for a resource monitor, the monitor has no quota and will never reach 100% usage within the specified interval.

Default: No value (i.e. no credit quota)

FREQUENCY = MONTHLY | DAILY | WEEKLY | YEARLY | NEVER

The frequency interval at which the credit usage resets to 0.

If you set a frequency for a resource monitor, you must also set START_TIMESTAMP.

If you specify NEVER for the frequency, the credit usage for the warehouse does not reset.

Default: No value (i.e. legacy behavior, whereby the credit quota resets at the beginning of each calendar month)

START_TIMESTAMP = timestamp | IMMEDIATELY

The date and time when the resource monitor starts monitoring credit usage for the assigned warehouses.

If you set a timestamp for a resource monitor, you must also set FREQUENCY.

Default: No value (i.e. legacy behavior, whereby the resource monitor starts monitoring warehouses immediately)

END_TIMESTAMP = timestamp

The date and time when the resource monitor suspends the assigned warehouses.

Default: No value (i.e. no warehouse suspension date)

TRIGGERS ... (aka actions)

Specifies one or more triggers for the resource monitor. Each trigger definition consists of the following:

ON threshold PERCENT

A numeric value specified as a percentage of the credit quota for the resource monitor; values larger than 100 are supported. Once usage reaches this threshold for the current month, the trigger fires.

DO SUSPEND | SUSPEND_IMMEDIATE | NOTIFY

Specifies the action performed by the trigger when the threshold is reached:

  • SUSPEND: Suspend all assigned warehouses while allowing currently running queries to complete. No new queries can be executed by the warehouses until the credit quota for the resource monitor is increased. In addition, this action sends a notification to all users who have enabled notifications for themselves.

  • SUSPEND_IMMEDIATE: Suspend all assigned warehouses immediately and cancel any currently running queries or statements using the warehouses. In addition, this action sends a notification to all users who have enabled notifications for themselves.

  • NOTIFY: Send an alert (to all users who have enabled notifications for themselves), but do not take any other action.

Default: No value (i.e. resource monitor performs no actions)

Usage Notes

  • Triggers are optional; however, at least one trigger must be added to a resource monitor before it can perform any actions.

  • Each resource monitor supports up to a maximum of 5 NOTIFY action triggers.

  • After a resource monitor is created, it must be assigned to a warehouse or account before it can perform any monitoring actions:

  • To view all resource monitors created in your account and their assignment, use the SHOW RESOURCE MONITORS command. The command output displays NULL in the level column for resource monitors that are not assigned to the account or any warehouses and, therefore, are not monitoring any credit usage.

Important

To receive notifications generated by resource monitors, account administrators must explicitly enable notifications in their preferences. In addition, to receive email notifications, account administrators must have a verified email in their preferences. Preferences can only be set in the Snowflake web interface. For more information, see Enabling Receipt of Notifications.

Examples

Create a resource monitor named limiter with 3 triggers:

CREATE OR REPLACE RESOURCE MONITOR limiter WITH CREDIT_QUOTA=5000
    TRIGGERS ON 75 PERCENT DO NOTIFY
             ON 100 PERCENT DO SUSPEND
             ON 110 PERCENT DO SUSPEND_IMMEDIATE;