Index Internals - RowStore, ColumnStore, and NoStore

Explore the intricacies of SQL Server index internals in this comprehensive 59-minute conference talk by Kalen Delaney at PASS Data Community Summit. Delve into the world of RowStore, ColumnStore, and NoStore indexes, understanding their structures, use cases, and performance implications. Learn about B-Trees, leaf levels, and when to use disk-based B-Tree indexes. Discover the basics of Columnstore indexes and their optimal application scenarios. Investigate In-Memory OLTP and hash indexes, gaining insights into their benefits and appropriate usage. Compare different index types, explore fragmentation issues, and understand statistics and maintenance requirements. Gain valuable knowledge to optimize database performance and make informed decisions about index selection in various scenarios.

Intro
Technical Assistance
Kalen Delaney Bio
Precon Abstract
Session Overview
Rowstore Indexes
B-Trees
What is in the Leaf Level?
When to Use: Disk-based B-Tree Indexes
Segments and Rowgroups
Columnstore Index Basics
When to Use: Columnstore Indexes
In-memory Indexes (Nostore)
Why In-Memory OLTP?
Hash Indexes
When to Use: In-Memory Indexes Only on memory-optimized tables
Comparisons
Fragmentation
Statistics
Maintenance
Session Summary Rowstore
Like What You've Heard?