Performance Concepts: Virtual Warehouses Flashcards
Virtual Warehouse
A virtual warehouse is a named abstraction for a Massively Parallel Processing (MPP) compute cluster.
Virtual Warehouses execute what operations?
- DQL operations (SELECT)
- DML operations (UPDATE)
- Data Loading Operations (COPY INTO)
As a user you only interact with the _________ warehouse object not the underlying compute resources.
named
Spin up and shut-down a virtually __ number of warehouses without resource contention.
unlimited
Virtual Warehouse configuration can be changed ___________.
on-the-fly
Virtual Warehouses contain local ____ storage used to sore ______ data retrieved from the ________ layer.
SSD; raw; storage
Virtual Warehouses are created via the _____________ or through _______ commands.
Snowflake UI; SQL
DROP WAREHOUSE statement
DROP WAREHOUSE MY_WAREHOUSE;
CREATE WAREHOUSE statement
CREATE WAREHOUSE MY_MED_WH
WAREHOUSE_SIZE=’MEDIUM’;
ALTER WAREHOUSE SUSPEND statement
ALTER WAREHOUSE MY_WH SUSPEND;
ALTER WAREHOUSE resize statement
ALTER WAREHOUSE MY_WH_2 SET
WAREHOUSE_SIZE=MEDIUM;
CREATE HOUSE with minimum and maximum cluster statement
CREATE WAREHOUSE MY_WH_3
MIN_CLUSTER_COUNT=1
MAX_CLUSTER_COUNT=3
SCALING_POLICY=STANDARD;
Virtual Warehouse States
- Started
- Suspended
- Resizing
By default when a Virtual Warehouse is created it is in the ________________ state.
STARTED
Suspending a Virtual Warehouse puts it in the ________________ state, removing the compute nodes from a warehouse.
SUSPENDED
Resuming a Virtual Warehouse puts in back into the STARTED state and can execute queries.
STARTED
AUTO SUSPEND definition
Specifies the number of seconds of inactivity after which a warehouse is automatically suspended.
AUTO SUSPEND statement
CREATE WAREHOUSE MY_MED_WH
AUTO_SUSPEND=300;
AUTO RESUME definition
Specifies whether to automatically resume a warehouse when a SQL statement is submitted to it.
AUTO RESUME statement
CREATE WAREHOUSE MY_WED_WH
AUTO_RESUME=TRUE;
INITIALLY SUSPENDED definition
Specifies whether the warehouse is created initially in the ‘Suspended’ state.
INITIALLY SUSPENDED statement
CREATE WAREHOUSE MY_MED_WH
INITIALLY_SUSPENDED=TRUE;
Virtual Warehouses can be created in what 10 t-shirt sizes?
x-Small, Small, Medium, Large, x-Large, 2x-Large, 3x-Large, 4x-Large, 5x-Large, 6x-Large
Underlying compute power approximately ______ with each virtual warehouse size increase.
doubles
Virtual Warehouse Billing per Warehouse Size
Resource Monitors
-Resource monitors are objects allowing users to set credit limits on user managed warehouses
Resource Monitors can be set on either the ________ or __________ warehouse level.
account; individual
Resource Monitors limits can be set for a ________ __________ or ______ _______.
specified interval; date range
When Resource Monitors limits are reached an __________ can be triggered, such as notify user or suspend warehouse.
action
Resource Monitors can only be created by _______________ _______________.
account administrators
Create a resource statement with credit quota of 100 and a monthly frequency and notify trigger on 50%, notify trigger on 75%, suspend on 95%, suspend immediately at 100%, and a time stamp of 1/4/23
CREATE RESOURCE MONITOR ANALYSIS_RM
WITH CREDIT QUOTA=100
FREQUENCY=MONTHLY
START_TIMESTAMP=’2021-01-04 00:00 GMT’
TRIGGERS ON 50 PERCENT DO NOTIFY
ON 75 PERCENT DO NOTIFY
ON 95 PERCENT DO SUSPEND
ON 100 PERCENT DO SUSPEND_IMMEDIATE;
Scaling up a Virtual Warehouse is intended to __________ query performance.
improve
Virtual Warehouses can be manually resized via the __________ _____ or _____ __________.
Snowflake UI, SQL commands
Resizing a running warehouse does not impact running queries. The additional compute resources are used for __________ and ________ queries.
queued, new
Decreasing the size of a running warehouse ____________ compute resources from the warehouse and ___________ the warehouse cache.
removes, clears
Alter warehouse statement for MY_WH with a large warehouse size
ALTER WAREHOUSE MY_WH
SET WAREHOUSE_SIZE=LARGE;
Multi-cluster warehouse definition
a named group of virtual warehouses which can automatically scale in and out based on the number of concurrent users/queries.
MIN_CLUSTER_COUNT definition
specifies the minimum number of warehouses for a multi-cluster warehouse
MAX_CLUSTER_COUNT definition
specifies the maximum number of warehouses for a multi-cluster warehosue
Setting MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT to the same value will put the multi-cluster warehouse in ____________________ mode.
MAXIMIZED
Setting MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT to different values will put the multi-cluster warehouse in __________________ mode.
AUTO-SCALE
Describe the Standard Scaling Policy
Describe the Economy Scaling Policy
The total credit cost of a multi-cluster warehouse is the ______ of all the ____________ running warehouses that make up that cluster.
sum, individual
The maximum number of credits a multi-cluster can consume is the _______ of warehouses __________ by the ________ credit rate of the size of the warehousees.
number, multiplied, hourly
MAX_CONCURRENCY_LEVEL definition
Specifies the number of concurrent SQL statements that can be executed against a warehouse before either it is queued or additional compute power is provided.
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS definition
Specifies the time in seconds a SQL statement can be queued on a warehouse before its aborted.
STATEMENT_TIMEOUT_IN_SECONDS definition
It specifies the time, in seconds, after which any running SQL statement on a warehouse is aborted.
