page35
15
Figure 1-2.
Warehouse events history
In addition to determining warehouse runtime, you can also see
CLUSTER_NUMBER
indicating scaling out; I discuss warehouse clustering later.
Optimally sizing warehouses and the clustering factor provides a significant
opportunity to reduce costs, a theme I will return to later.
Optimizer Approach
Many of us remember a legacy “rules-based” approach to defining an efficient execution
plan, a suite of predefined rules applied to the SQL statement used to derive the optimal
execution path. The “rules-based” approach did not require statistics; if the rules
quality and coverage did not cater for the SQL statement being executed, then poor
performance would most likely ensue.
For most if not all RDBMSs, “rules-based” optimizers have largely been replaced by
“cost-based” optimizers where real-world statistics inform sophisticated algorithms to
evaluate and select execution plans. Cost-based optimizers generally outperform rule-
based approaches in terms of query optimization effectiveness and adaptability.
Snowflake has adopted a simplified approach to delivering their optimizer by only
supplying a cost-based optimizer. Furthermore, for their optimizer, Snowflake by design
leaves as little to the user as possible. For example, unlike some legacy RDBMS, you
cannot add hints to influence the generated optimizer query plan.
Plan stability is essential for predictable repeat performance, and all RDBMS vendors
strive to achieve this objective. Snowflake is no exception where the key objective is to
create a robust cost-based optimizer delivering stable execution plans. Additionally,
Snowflake focuses on optimizations for analytics workloads and support for all data
models including third normal form, data vault, and star schema. You also know
Snowflake has implemented many non-cost-based optimizations and continues to
work on eliminating nonperformant edge cases too, all part of the continual product
improvements.
ChaptEr 1
tuning thE SnowFlakE Data ClouD