D2.2 - EXPLAIN VIRTUAL WAREHOUSE BEST PRACTICES

Question 1

‘Scale Up’ compared to ‘Scale Out’

Answer 1

Scale up: Larger warehouse

Scale Out: Creating or updating to a multi-cluster (requires enterprise edition)

Question 2

Benefits of resizing a warehouse

Answer 2

• Generally improves query performance, particularly for larger, more complex queries
• It CAN but is not meant to help reduce the queuing that occurs if a warehouse does not have enough compute resources to process all the queries that are submitted concurrently; warehouse clustering is recommended for queueing issues

Question 3

Benefits of a Multi-Cluster warehouse

Answer 3

• Designed specifically for handling queueing and performance issues related to large numbers of concurrent users and/or queries
• Additionally, multi-cluster warehouse can help automate this process if your number of users/queries tend to fluctuate

Question 4

Types of Virtual Warhouse

Answer 4

• 10 Sizes of virtual warehouse
• XS, S, M, L, XL, 2XL, 3XL, 4XL, 5XL, 6XL
• Each warehouse doubles computing power and doubles the number of credits billed per hour
• Warehouses are only billed when running. They bill for a 60-second minimum and then every second after.

Question 5

The Key to using data warehouses effectively?

Answer 5

• Experiment with different types of queries and different warehouse sizes to determine the combos that best me your specific query needs and workloads
• Don’t focus on warehouse size. Snowflake utilizes per-second billing, so you can run larger warehouses (Large, XL, 2XL etc) and simply suspend them when not in use

Question 6

How to view load monitoring chart?

Answer 6

You must have a role that has the MONITOR privilege on the warehouse

Question 7

What does the web interface provide?

Answer 7

• A query load chart that depicts concurrent queries processed by a warehouse over a two-week period.
• The warehouse query load measures the average number of queries that were running or queued within a specific interval

Question 8

When you notice a query in running slowly?

Answer 8

• Check whether an overloaded warehouse is causing the query to compete for resources or get queued:
• IF running query load is high or there is queuing, add another warehouse
• IF the running query load is low but query performance is still slow, resize warehouse for more computer resources