Performance Concepts: Virtual Warehouses Flashcards

1
Q

Virtual Warehouse

A

A virtual warehouse is a named abstraction for a Massively Parallel Processing (MPP) compute cluster.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Virtual Warehouses execute what operations?

A
  • DQL operations (SELECT)
  • DML operations (UPDATE)
  • Data Loading Operations (COPY INTO)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

As a user you only interact with the _________ warehouse object not the underlying compute resources.

A

named

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Spin up and shut-down a virtually __ number of warehouses without resource contention.

A

unlimited

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Virtual Warehouse configuration can be changed ___________.

A

on-the-fly

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Virtual Warehouses contain local ____ storage used to sore ______ data retrieved from the ________ layer.

A

SSD; raw; storage

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Virtual Warehouses are created via the _____________ or through _______ commands.

A

Snowflake UI; SQL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

DROP WAREHOUSE statement

A

DROP WAREHOUSE MY_WAREHOUSE;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

CREATE WAREHOUSE statement

A

CREATE WAREHOUSE MY_MED_WH
WAREHOUSE_SIZE=’MEDIUM’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

ALTER WAREHOUSE SUSPEND statement

A

ALTER WAREHOUSE MY_WH SUSPEND;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

ALTER WAREHOUSE resize statement

A

ALTER WAREHOUSE MY_WH_2 SET
WAREHOUSE_SIZE=MEDIUM;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

CREATE HOUSE with minimum and maximum cluster statement

A

CREATE WAREHOUSE MY_WH_3
MIN_CLUSTER_COUNT=1
MAX_CLUSTER_COUNT=3
SCALING_POLICY=STANDARD;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Virtual Warehouse States

A
  • Started
  • Suspended
  • Resizing
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

By default when a Virtual Warehouse is created it is in the ________________ state.

A

STARTED

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Suspending a Virtual Warehouse puts it in the ________________ state, removing the compute nodes from a warehouse.

A

SUSPENDED

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Resuming a Virtual Warehouse puts in back into the STARTED state and can execute queries.

A

STARTED

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

AUTO SUSPEND definition

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

AUTO SUSPEND statement

A

CREATE WAREHOUSE MY_MED_WH
AUTO_SUSPEND=300;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

AUTO RESUME definition

A

Specifies whether to automatically resume a warehouse when a SQL statement is submitted to it.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

AUTO RESUME statement

A

CREATE WAREHOUSE MY_WED_WH
AUTO_RESUME=TRUE;

21
Q

INITIALLY SUSPENDED definition

A

Specifies whether the warehouse is created initially in the ‘Suspended’ state.

22
Q

INITIALLY SUSPENDED statement

A

CREATE WAREHOUSE MY_MED_WH
INITIALLY_SUSPENDED=TRUE;

23
Q

Virtual Warehouses can be created in what 10 t-shirt sizes?

A

x-Small, Small, Medium, Large, x-Large, 2x-Large, 3x-Large, 4x-Large, 5x-Large, 6x-Large

24
Q

Underlying compute power approximately ______ with each virtual warehouse size increase.

A

doubles

25
Q

Virtual Warehouse Billing per Warehouse Size

A
26
Q

Resource Monitors

A

-Resource monitors are objects allowing users to set credit limits on user managed warehouses

27
Q

Resource Monitors can be set on either the ________ or __________ warehouse level.

A

account; individual

28
Q

Resource Monitors limits can be set for a ________ __________ or ______ _______.

A

specified interval; date range

29
Q

When Resource Monitors limits are reached an __________ can be triggered, such as notify user or suspend warehouse.

A

action

30
Q

Resource Monitors can only be created by _______________ _______________.

A

account administrators

31
Q

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

A

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;

32
Q

Scaling up a Virtual Warehouse is intended to __________ query performance.

A

improve

33
Q

Virtual Warehouses can be manually resized via the __________ _____ or _____ __________.

A

Snowflake UI, SQL commands

34
Q

Resizing a running warehouse does not impact running queries. The additional compute resources are used for __________ and ________ queries.

A

queued, new

35
Q

Decreasing the size of a running warehouse ____________ compute resources from the warehouse and ___________ the warehouse cache.

A

removes, clears

36
Q

Alter warehouse statement for MY_WH with a large warehouse size

A

ALTER WAREHOUSE MY_WH
SET WAREHOUSE_SIZE=LARGE;

37
Q

Multi-cluster warehouse definition

A

a named group of virtual warehouses which can automatically scale in and out based on the number of concurrent users/queries.

38
Q

MIN_CLUSTER_COUNT definition

A

specifies the minimum number of warehouses for a multi-cluster warehouse

39
Q

MAX_CLUSTER_COUNT definition

A

specifies the maximum number of warehouses for a multi-cluster warehosue

40
Q

Setting MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT to the same value will put the multi-cluster warehouse in ____________________ mode.

A

MAXIMIZED

41
Q

Setting MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT to different values will put the multi-cluster warehouse in __________________ mode.

A

AUTO-SCALE

42
Q

Describe the Standard Scaling Policy

A
43
Q

Describe the Economy Scaling Policy

A
44
Q

The total credit cost of a multi-cluster warehouse is the ______ of all the ____________ running warehouses that make up that cluster.

A

sum, individual

45
Q

The maximum number of credits a multi-cluster can consume is the _______ of warehouses __________ by the ________ credit rate of the size of the warehousees.

A

number, multiplied, hourly

46
Q

MAX_CONCURRENCY_LEVEL definition

A

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.

47
Q

STATEMENT_QUEUED_TIMEOUT_IN_SECONDS definition

A

Specifies the time in seconds a SQL statement can be queued on a warehouse before its aborted.

48
Q

STATEMENT_TIMEOUT_IN_SECONDS definition

A

It specifies the time, in seconds, after which any running SQL statement on a warehouse is aborted.