page53
34
Warehouses
Each Snowflake node is a warehouse, and I discuss warehouses and their use later within
this book. To not get bogged down in too much detail, for now just remember an extra
small (XSmall) warehouse has eight CPUs and an associated cache, about 16 to 24 GB
RAM, local SSD storage, and remote attached storage. For every T-shirt size we increase
our warehouse to use, the number of CPUs doubles and memory increases too. These
values will become very important as we progress through this book.
i use the term execution unit to respect the multithreaded/multicore/multimode
operation of the underlying hardware; i don’t always know the CSp hardware
capabilities.
In the old world of on-prem databases, we had an appreciation of the physical
hardware, including the number of available processing units, minus one for the
operating system. Memory constraints also applied, and these limitations were
judiciously managed by others. In the new world of cloud computing, we are abstracted
away from the physical hardware and largely free to allocate resources on demand.
Without wanting to sound flippant, don’t get absorbed in the details. Instead, you
should accept there are some limitations to warehouses, but the elastic nature allows us
to reconsider our approach by allocating resources on demand.
Single Instruction, Multiple Data (SIMD)
Snowflake query execution implements SIMD instructions, a technique optimized for
data-level parallelism where each processing unit performs identical instructions on
different data.
In Figure 2-3 we assume four processing units all performing the same “times two”
operation against different data for two execution cycles.
Chapter 2
the Query Optimizer