Chapter 8 Notes – Index Usage (Pages 21-28)

Generic Plan Using OPTIMIZE FOR UNKNOWN

• Alternative to parameter‐sniffed or fully parameterized plans.

  • Hint syntax: OPTION (OPTIMIZE FOR (@OrderQty UNKNOWN)).

  • Optimizer falls back to density vector for cardinality estimation.

  • Example from text: always estimates $2958.95$ rows regardless of real value.

• Trade-off: "good-enough" plan for all executions vs. optimal plan for some.

  • Can avoid extreme regressions but may under-perform for skewed values.

Columnstore Indexes – General Overview

• Introduced in SQL Server 2012 as a new physical index type.

• Store data by column instead of by row.

  • Traditional indexes = B-tree (rowstore).

  • Columnstore = column segments + optional delta stores (rowstore) & metadata B-trees.

• Two variants

  • Clustered Columnstore Index (CCI) – replaces heap/clustered rowstore; whole table stored column-wise.

  • Nonclustered Columnstore Index (NCCI) – secondary index that co-exists with rowstore tables.

• Primary workload target: large fact tables (millions–billions of rows) executing analytic, reporting, & aggregation queries.

  • Small, OLTP-style lookups (< 100 000 rows) usually slower because entire row must be reconstructed column-by-column.

Storage Concepts & Rowgroups

• Partition → multiple rowgroups (≈ 1 000 000 rows each).

• Each rowgroup compressed independently into column segments.

• Benefits rely on filling rowgroups; Microsoft guidance:

  • "Each partition should have ≥ 1 000 000 rows" to maximize compression & performance.

Compression & Performance Facts

• High compression → fewer logical/physical reads; improves memory bandwidth.

• Skipping unnecessary columns reduces I/O because only referenced segments are touched.

• For DW-sized tables, combination of rowgroup elimination + batch mode can yield orders-of-magnitude speed-ups.

Batch Mode vs. Row Mode

• Columnstore enables a new Batch Mode execution engine (vectorized processing).

  • Processes sets of ~900 values at once instead of row-by-row.

• Operator property sheet:

  • Estimated Execution Mode

  • Actual Execution Mode
    Both should read Batch for maximum benefit.

  • If Actual falls back to Row while Estimated = Batch: usually due to spills (especially in SQL 2012).

Further Learning Resources (from transcript)

• Microsoft docs overview – http://bit.ly/1djYOCW

• Hugo Kornelis “Stairway to Columnstore” – http://bit.ly/2CBiXoQ

• “What’s New” feature table – http://bit.ly/2oD9keB

• Niko Neugebauer’s blog series – http://www.nikoport.com/columnstore/

Demo 1 – Aggregation Query Without Columnstore

• Table: Production.TransactionHistory (≈ 113 k rows).

• Query (Listing 8-9) aggregates COUNT, SUM, AVG by ProductID joined to Production.Product.

  • No WHERE clause → clustered index scan.

  • Plan: Clustered Index Scan → Hash Match (Aggregate) → compute scalar for $\text{AVG}=\frac{\text{SUM}}{\text{COUNT}}$ → Hash Match (Join).

  • Returned 441 rows.

  • Performance observed: 127 ms, 803 logical reads.

Demo 2 – Adding a Nonclustered Columnstore Index

CREATE NONCLUSTERED COLUMNSTORE INDEX ix_csTest
ON Production.TransactionHistory (
  ProductID,
  Quantity,
  ActualCost,
  ReferenceOrderID,
  ReferenceOrderLineID,
  ModifiedDate);

• Re-running same query:

  • Time ↓ from 127 ms → 55 ms.

  • Logical reads ↓ 803 → 84 (variance expected due to compression layout).

• Plan differences (Fig 8-14)

  • Columnstore Index Scan replaces rowstore scan.

  • Adaptive Join may appear (requires compatibility level ≥ 140).

Aggregate Pushdown (SQL 2016+)

• Columnstore Index Scan can internally compute partial or full aggregates.

  • Operator properties show:

  • Estimated Rows = 113 443.

  • Actual Number of Rows = 0 (nothing returned upward).

  • Actual Number of Locally Aggregated Rows = 113 443.

• Hash Match( Aggregate ) receives pre-aggregated rows, reducing memory & CPU pressure.

Seek Absence & Predicate Pushdown

• Columnstore indexes are unsorted; no Seek operator exists.

• Filtering relies on Predicate Pushdown inside the scan.

  • Example query (Listing 8-11) added WHERE th.TransactionID > 150000.

  • Properties show pushed predicate.

• Benefit: may enable rowgroup elimination (a.k.a. segment skipping).

  • SET STATISTICS IO reports Segment reads vs. Segment skipped counts.

  • With 1 rowgroup (small table) → skips = 0; with large tables could skip many.

Memory-Optimized Indexes (Hekaton)

• Part of Memory-Optimized Tables (SQL 2014+).

• Two index types (memory only):

  • Hash Index

  • Array of buckets → O(1) lookup of specific equality values.

  • Best for singleton key searches.

  • Range Index (Bw-tree)

  • Navigable structure similar to B-tree but latch-free & log-friendly.

  • Supports range predicates, inequality, ORDER BY, etc.

• Design goals: eliminate page latches, exploit memory, sustain very high insert/update throughput.

• Additional use case: replacing table variables with durable or non-durable memory-optimized tables.

• Caveats & learning:

  • Feature restrictions (foreign keys, constraints, MAX types, etc.)—see MS Docs: http://bit.ly/2EQl2Lc.

  • Deep dive: Kalen Delaney’s book – http://bit.ly/2BpDxXI.

Demo Setup (Listing 8-12)

• Creates test database with filegroup for memory-optimized data.

• Creates 3 memory-optimized tables (copies of AdventureWorks 2014 tables).

• Be sure to adjust $\text{FILENAME}$, $\text{SIZE}$, $\text{FILEGROWTH}$ to your environment.