page287
375
SHOW warehouses;
ALTER WAREHOUSE compute_wh SET auto_suspend = 60;
Unless there are compelling reasons to retain an active, running warehouse, repeat
for every warehouse in the account.
Warehouse Size
Don’t be frightened of using a Large or bigger warehouse. They can be more time and
cost effective than using a smaller warehouse. Note that failure to set the warehouse size
correctly will result in excessive consumption charges.
Conversely, and in line with the best practices outlined in this book, if your code
has been properly tuned, then you may be able to reduce your warehouse size while
achieving the same or better process runtimes. Reducing warehouse size by one size
halves the runtime cost.
Always seek to parallelize processes to concurrently use all the available warehouse
processing units.
Warehouse scaling must also be considered; I will discuss warehouse scaling in
Chapter 6.
Warehouse Usage
There are many considerations when optimally sizing and using your warehouses. Some
of these are as follows:
•
Is the workload consistent with historical “steady-state” workloads?
•
How many concurrent workloads are running against the warehouse?
•
What is your warehouse concurrency set to?
•
Are workloads queueing?
•
Is warehouse clustering enabled and, if so, to what degree?
•
For each workload, are any workloads spilling to disk?
•
Is object locking evident?
•
Does the warehouse run too frequently?
•
Are too many warehouses of same size declared?
ChapTer 10
OpTimizing perfOrmanCe