page289
377
ALTER WAREHOUSE <warehouse_name> SET MIN_CLUSTER_COUNT = 1;
ALTER WAREHOUSE <warehouse_name> SET MAX_CLUSTER_COUNT = 4;
Warehouses operating in maximized mode are identified where the maximum and
minimum number of clusters are the same value. In this scenario, all clusters are started
at warehouse instantiation and may be useful for known, static workloads.
ALTER WAREHOUSE <warehouse_name> SET MIN_CLUSTER_COUNT = 2;
ALTER WAREHOUSE <warehouse_name> SET MAX_CLUSTER_COUNT = 2;
You can find more information on warehouse maximized mode at
https://
docs.snowflake.com/en/user-guide/warehouses-multicluster#maximized-vs-
auto-scale
.
Bind Variables
Bind variables enable query reuse. The first time a SQL statement is seen by the
optimizer, a hard-parse is performed, all subsequent query submissions reuse the
original execution plan and substitute values for the bound variables.
Overall query performance suffers where SQL statements are always hard-parsed,
the optimizer cannot re-use queries. Bind variables are always considered best practice
where the main body of a query remains static. Implementation costs for implementing
bind variables result in both lower execution costs and reduced code maintenance
overheads.
Eliminate SELECT *
Snowflake only access those explicitly named attributes from base tables. SELECT * is an
obvious candidate for removal and replacement with explicitly named attributes. In real-
world testing we observe performance improvements by removing SELECT *.
Replace SELECT * with only those attributes required to satisfy the query. Do not add
extraneous or unused attributes as these result in extra workload.
SELECT * with UNION (not UNION ALL) results in a full table scan for each side of
the UNION.
ChapTer 10
OpTimizing perfOrmanCe