page288
376
•
Is there low warehouse cache reuse?
•
Is there an incorrect
auto_suspend
setting?
•
Is there an artificial warehouse size constraint imposed?
•
Are files correctly sized for ingestion?
•
Is the warehouse correctly sized for the workload?
•
Is serial or parallel logging implemented?
•
Are warehouse scaling policies appropriate for the operating
environment?
•
Are warehouse modes correct for the expected workload?
To reduce the number of warehouses, use query tags when consolidating workloads
onto warehouses.
Warehouse Scaling Policy
By default, multicluster warehouses are created with the Standard scaling policy.
While appropriate for production environments, most nonproduction environment
warehouses will benefit from using the economy scaling policy to achieve a better
balance of speed and performance. With the economy scaling policy, nonproduction
environments can tolerate a higher degree of queueing to ensure greater warehouse
processing unit utilization.
ALTER WAREHOUSE <warehouse_name> SET SCALING_POLICY = 'ECONOMY';
You can find more information on warehouse scaling policies at
https://docs.
snowflake.com/en/user-guide/warehouses-multicluster#setting-the-scaling-
policy-for-a-multi-cluster-warehouse
.
Warehouse Mode
Warehouses operating in auto-scale mode are identified where the maximum and
minimum number of clusters have different values. In this scenario, a single cluster is
started at warehouse instantiation with further clusters starting (subject to maximum
clusters setting) according to workload. Auto-scale is the most common warehouse
mode and is useful for varying workloads.
ChapTer 10
OpTimizing perfOrmanCe