page42
22
expectation for object metadata to remain static. Best practice dictates both schema and
object structural changes should be made only in accordance with a rigorous change
control process. Introspection calls should therefore be able to rely upon a relatively
static suite of metadata.
In practice, you have found some Snowflake metadata lookups run slower than
expected when compared to an alternative RDBMS. The root cause appears to be
unexpected or unpredictable changes made to object definitions causing ad hoc
metadata changes. You might experience this phenomena where self-service has been
implemented for end users to own, manage, and maintain their own schemas where
unpredictable and ungoverned schema changes occur.
Further investigation is required as you cannot yet categorically identify the root
cause of slow-running metadata queries. I suggest queries using the account usage store
may be improved by making local copies of referenced views into tables (thanks to Nadir
Doctor for this tip). There is further sparse anecdotal evidence of this phenomena that
may be obtained using common Internet search engines.
Optimizer Statistics
Optimizer statistics are information and data values collected about the database
objects used by the query optimizer to inform decisions on how to execute SQL queries
efficiently. In contrast with some legacy RDBMSs, Snowflake guarantees the optimizer
statistics are always up-to-date; there is no delay. And also unlike some legacy RDBMSs,
for Snowflake there is no exposed capability to collect, delete, or manage statistics; this is
by design.
Snowflake captures the following statistics:
•
Table and micro-partition
•
Row count
•
Size in bytes (including compression information)
•
File reference
•
Table version
ChaptEr 1
tuning thE SnowFlakE Data ClouD