Think Like a Terminator - Internals of Classic SQL Server Indexes

Dive into the internals of classic SQL Server indexes in this 53-minute conference talk from PASS Data Community Summit. Explore clustered and non-clustered indexes from a computer's perspective, learning how SQL Server allocates space and utilizes indexes for simple select operations. Discover techniques to examine page binary code using DBCC IND and DBCC PAGE commands, gaining insights into index performance. Address common index-related issues like page splits and fragmentation, and learn strategies to mitigate them using fill factor. Compare index rebuild and reorganize commands, understanding when to apply each. Gain comprehensive knowledge to create and maintain optimal performing indexes, enhancing your skills as a DBA or database developer to improve application performance.

Introduction
Disclaimer
Database file structure
Why use indexes
Clustered indexes
Clustered index details
Clustered index demo
Page splits
Fill factor
Fill factor test
Nonclustered indexes
Clustered and covert indexes
No clustered index
Potential problems