Why is Batch Mode Fast?
summary
Batch mode runs columnstore data in cache‑sized batches, using vector and new CPU instructions so analytics run far fewer cycles and fewer mispredicted branches. Measured example counting ~8 billion bits goes from 4.3s → 2.6s → 1.3s → 0.6s as algorithms move from bitloops to lookup to popcount to vector popcount on Sapphire Rapids. Work includes AVX‑512 & vpopcnt kernels, operator rewrites, and parallelization that scales to 72 cores, delivering 10–100× analytic speedups and top TPC‑H results for SQL Server 2022.
AI-written summary · about
chapters (15)
- 0:00Introduction and speaker background
- 1:04Evolution of SQL Server hardware
- 5:15Modern CPU complexity and performance
- 11:56Instructions per cycle and chip efficiency
- 15:11Bit counting algorithms and CPU instructions
- 20:06Branch prediction and CPU efficiency
- 24:40Summary of CPU optimization strategies
- 25:43Introduction to batch mode in SQL Server
- 30:53Row mode vs batch mode execution engines
- 35:24Batch size design and cache optimization
- 38:13Parallel query scaling in batch mode
- 41:27Measuring SQL Server performance with TPC benchmarks
- 45:17AVX instructions and vectorized processing
- 47:05Batch mode optimizations in SQL Server 2022
- 49:11Conclusion and Q&A
related talks
19mSpark vs. Accelerated Compute: A Balanced Look at Data Processing Strategies
18mThe "New Normal" or An Evolution
30mParticle physics, 10,000 times faster
48mAnalytics Architectures on Microsoft Fabric
52mWriting fast C code for a modern CPU (and applying it to PostgreSQL)
15m100x faster data app experiences delivered cost-effectively
53mOptimizing your Azure SQL Database workloads
19mSpeed up transparent performance with Exadata: What, when, how, and why