page161
247
© Andrew Carruthers 2024
A. Carruthers, Tuning the Snowflake Data Cloud, https://doi.org/10.1007/979-8-8688-0379-6_7
CHAPTER 7
Search Optimization
Service
Earlier chapters discussed clustering, materialized views, dynamic tables, and the Query
Acceleration Service (QAS). In this chapter I cover the Search Optimization Service
(SOS) as an alternative but complementary approach.
At a fundamental level, query optimization resolves micro-partition pruning to
reduce the number of micro-partitions accessed for queries. Viewed from a pure micro-
partition pruning perspective, without considering summaries and aggregates, SOS
adopts a different approach to enable micro-partition pruning, thereby extending the
range of options available to improve query optimizer efficiency.
SOS is a little-understood but very powerful Snowflake service focused on pre-
building optimized data structures called search access paths maintained via serverless
compute. As you will discover in this chapter, search access paths only reference
micro-partitions containing explicitly referenced values. For those familiar with legacy
RDBMSs, you might consider search access paths to be single-attribute, index-like
constructs as they perform similar functionality.
Every serverless compute capability incurs compute cost, and SOS also incurs
additional storage costs; therefore, we advise both caution with thorough investigation
and testing of appropriate use cases before enabling SOS in your production
applications.
Not every query will benefit from SOS; you must align SOS enablement with
optimal consumption usage. Figure 7-1 suggests where each serverless compute
feature offers optimal benefits; note that SOS is targeted at data consumption from the
Presentation layer.