~/talk

Columnstore Index Compression in a Nutshell

on YouTube 20m
Talkat SQLBits 2023 · 2023-03-14..03-18· [databases, data-engineering]

speaker

name:Edward Pollack
links:none on file

resources

video:https://youtu.be/SiNj_fnZDr8
slides:none on file

summary

Describes how SQL Server stores analytic data in rowgroups (up to 2^20 ≈ 1M rows) with one column per segment compressed separately, using dictionary and value encoding, then reorders rows with vertipack optimization and applies xVelocity run‑length or bit‑array compression. Demonstrates DMVs (sys.column_store_segments, sys.column_store_dictionaries) to inspect sizes, shows the 16 MB dictionary limit that can split rowgroups, cites typical compression of 10–30:1 and occasional 100:1, and notes rebuilds vs memory‑optimized tradeoffs.

AI-written summary · about

chapters (10)

  1. 0:00Introduction to speaker
  2. 1:00Columnstore index basics
  3. 2:43Fundamentals of compression
  4. 3:56Dictionary encoding process
  5. 5:57Value encoding
  6. 7:48Vertipaq optimization
  7. 10:01Physical compression methods
  8. 12:30Optimizing for compression
  9. 14:55DMV monitoring demonstration
  10. 18:33Conclusion and Q&A

related talks

~/search
type to search 6,249 conferences, 138,987 speakers and 185,172 talks
~/help
cmdctrl k
search every conference, speaker and series
/
focus the filter search
h
home
c
conferences
s
speakers
e
series
t
light / dark — the status bar button does the same
?
this help
esc
close this, then clear every filter — or just the search, while you are typing in it

Ignored while you are typing. Pages without a filter rail have no search to focus.