Categories:

Warehouse & Resource Monitor DDL

ALTER WAREHOUSE

Suspends or resumes a virtual warehouse, or aborts all queries (and other SQL statements) for a warehouse. Can also be used to rename or set/unset the properties for a warehouse.

See also:

CREATE WAREHOUSE , SHOW WAREHOUSES

Syntax

ALTER WAREHOUSE [ IF EXISTS ] [ <name> ] { SUSPEND | RESUME [ IF SUSPENDED ] }

ALTER WAREHOUSE [ IF EXISTS ] [ <name> ] ABORT ALL QUERIES

ALTER WAREHOUSE [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER WAREHOUSE [ IF EXISTS ] <name> SET [ objProperties ]
                                         [ objParams ]

ALTER WAREHOUSE [ IF EXISTS ] <name> UNSET { <property_name> | <param_name> } [ , ... ]

Where:

objProperties ::=
  WAREHOUSE_SIZE = XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE
  MAX_CLUSTER_COUNT = <num>
  MIN_CLUSTER_COUNT = <num>
  SCALING_POLICY = STANDARD | ECONOMY
  AUTO_SUSPEND = <num>
  AUTO_RESUME = TRUE | FALSE
  RESOURCE_MONITOR = <monitor_name>
  COMMENT = '<string_literal>'
objParams ::=
  MAX_CONCURRENCY_LEVEL = <num>
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <num>
  STATEMENT_TIMEOUT_IN_SECONDS = <num>

Properties/Parameters

name

Specifies the identifier for the warehouse to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Syntax.

Note

A warehouse identifier is required or optional depending on the following:

  • When resuming/suspending a warehouse or aborting queries for a warehouse, if a warehouse is currently in use for the session, the identifier can be omitted.

  • When renaming a warehouse or performing any other operations on a warehouse, the identifier must be specified.

SUSPEND | RESUME [ IF SUSPENDED ]

Specifies the action to perform on the warehouse:

  • SUSPEND removes all compute nodes from a warehouse and put the warehouse into a ‘Suspended’ state.

  • RESUME [ IF SUSPENDED ] brings a suspended warehouse to a usable ‘Running’ state by provisioning servers.

    The optional IF SUSPENDED clause specifies whether the ALTER WAREHOUSE command completes successfully when resuming a warehouse that is already running:

    • If omitted, the command fails and returns an error if the warehouse is already running.

    • If specified, the command completes successfully regardless of whether the warehouse is running.

ABORT ALL QUERIES

Aborts all the queries currently running or queued on the warehouse.

RENAME TO new_name

Specifies a new identifier for the warehouse; must be unique for your account.

For more details, see Identifier Syntax.

SET ...

Specifies one or more properties/parameters to set for the warehouse (separated by blank spaces, commas, or new lines):

WAREHOUSE_SIZE = string_constant

Specifies the size of the virtual warehouse. The size determines the number of servers in each cluster in the warehouse and, therefore, the number of credits consumed while the warehouse is running.

Valid values
  • XSMALL , 'X-SMALL'

  • SMALL

  • MEDIUM

  • LARGE

  • XLARGE , 'X-LARGE'

  • XXLARGE , X2LARGE , '2X-LARGE'

  • XXXLARGE , X3LARGE , '3X-LARGE'

  • X4LARGE , '4X-LARGE'

Default

XLARGE

Note

To use a value that contains a hyphen (e.g. '2X-LARGE'), you must enclose the value in single quotes, as shown.

MAX_CLUSTER_COUNT = num

Specifies the maximum number of server clusters for the warehouse.

Valid values

1 to 10

Note that specifying a value greater than 1 indicates the warehouse is a multi-cluster warehouse; however, the value can be only set to a higher value in Snowflake Enterprise Edition (or higher).

For more information, see Multi-cluster Warehouses.

Default

1 (single-cluster warehouse)

Tip

For Snowflake Enterprise Edition (or higher), we recommend always setting the value greater than 1 to help maintain high-availability and optimal performance of the warehouse. This also helps ensure continuity in the unlikely event that a cluster fails.

MIN_CLUSTER_COUNT = num

Specifies the minimum number of server clusters for the warehouse (only applies to multi-cluster warehouses).

Valid values

1 to 10

Note, however, that MIN_CLUSTER_COUNT must be equal to or less than MAX_CLUSTER_COUNT:

  • If both parameters are equal, the warehouse runs in Maximized mode.

  • If MIN_CLUSTER_COUNT is less than MAX_CLUSTER_COUNT, the warehouse runs in Auto-scale mode.

For more information, see Multi-cluster Warehouses.

Default

1

SCALING_POLICY = STANDARD | ECONOMY

Object parameter that specifies the policy for automatically starting and shutting down clusters in a multi-cluster warehouse running in Auto-scale mode.

For a detailed description of this parameter, see Setting the Scaling Policy for a Multi-cluster Warehouse.

AUTO_SUSPEND = num | NULL

Specifies the number of seconds of inactivity after which a warehouse is automatically suspended.

Valid values

Any number greater than 59 or NULL:

  • Setting a value less than 59 has no effect because the minimum amount of time a warehouse can run before it suspends is 1 minute.

  • Setting a NULL value means the warehouse never suspends.

Default

600 (the warehouse suspends automatically after 10 minutes of inactivity)

Important

Setting AUTO_SUSPEND value to NULL is not recommended, unless your query workloads require a continually running warehouse. Note that this can result in significant consumption of credit (and corresponding charges), particularly for larger warehouses.

For more details, see:doc:user-guide/warehouses-considerations.

AUTO_RESUME = TRUE | FALSE

Specifies whether to automatically resume a warehouse when a SQL statement (e.g. query) is submitted to it. If FALSE, the warehouse only starts again when explicitly resumed using ALTER WAREHOUSE or through the Snowflake web interface.

Valid values
  • TRUE: The warehouse resumes when a new query is submitted.

  • FALSE: The warehouse only resumes when explicitly resumed using ALTER WAREHOUSE or through the Snowflake web interface.

Default

TRUE (the warehouse resumes automatically when a SQL statement is submitted to it)

INITIALLY_SUSPENDED = TRUE | FALSE

Not applicable when altering a warehouse

RESOURCE MONITOR = rm_name

Specifies the identifier of a resource monitor that is explicitly assigned to the warehouse. When a resource monitor is explicitly assigned to a warehouse, the monitor controls the monthly credits used by the warehouse (and all other warehouses to which the monitor is assigned).

Valid values

Any existing resource monitor.

For more details, see Working with Resource Monitors.

Default

No value (no resource monitor assigned to the warehouse)

Tip

To view all resource monitors and their identifiers, use the SHOW RESOURCE MONITORS command.

COMMENT = 'string_literal'

Adds a comment or overwrites an existing comment for the warehouse.

MAX_CONCURRENCY_LEVEL = num

Object parameter that specifies the concurrency level for SQL statements (i.e. queries and DML) executed by a warehouse cluster. When the level is reached:

  • For a single-cluster warehouse or a multi-cluster warehouse (in Maximized mode), additional statements are queued until resources are available.

  • For a multi-cluster warehouse (in Auto-scale mode), additional clusters are started.

This parameter can be used in conjunction with STATEMENT_QUEUED_TIMEOUT_IN_SECONDS to ensure a warehouse is never backlogged.

For a detailed description of this parameter, see MAX_CONCURRENCY_LEVEL.

STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = num

Object parameter that specifies the time, in seconds, a SQL statement (query, DDL, DML, etc.) can be queued on a warehouse before it is canceled by the system.

This parameter can be used in conjunction with MAX_CONCURRENCY_LEVEL to ensure a warehouse is never backlogged.

For a detailed description of this parameter, see STATEMENT_QUEUED_TIMEOUT_IN_SECONDS.

STATEMENT_TIMEOUT_IN_SECONDS = num

Object parameter that specifies the time, in seconds, after which a running SQL statement (query, DDL, DML, etc.) is canceled by the system.

For a detailed description of this parameter, see STATEMENT_TIMEOUT_IN_SECONDS.

UNSET ...

Specifies one (or more) properties and/or parameters to unset for the database, which resets them to the defaults:

  • property_name

  • param_name

You can reset multiple properties/parameters with a single ALTER statement; however, each property/parameter must be separated by a comma. Also, when resetting a property/parameter, you only specify the name; no value is required.

Note

UNSET can be used to unset all the properties and parameters for a warehouse, except WAREHOUSE_SIZE, which can only be changed using SET.

Usage Notes

  • A warehouse does not need to be suspended to set or change any of its properties, including size.

  • When the warehouse size is changed, the change does not impact any statements, including queries, that are currently executing. Once the statements complete, the new size is used for all subsequent statements.

  • Suspending a warehouse does not abort any queries being processed by the warehouse at the time it is suspended. Instead, the warehouse completes the queries, then shuts down the servers used to process the queries. During this time period, the warehouse and its servers are in quiescing mode. When all the servers are shut down, the warehouse’s status changes to Suspended.

Examples

Rename warehouse wh1 to wh2:

ALTER WAREHOUSE IF EXISTS wh1 RENAME TO wh2;

Resume a warehouse named my_wh and then change the size of the warehouse while it is running:

ALTER WAREHOUSE my_wh RESUME;

ALTER WAREHOUSE my_wh SET warehouse_size=MEDIUM;