Index Fragmentation
Paul Randal
Dev Lead, Microsoft SQL Server

Introduction

Structural details of an index

How these structures are used

Index fragmentation

SQL Server 2000 DBCC SHOWCONTIG

SQL Server 2000 DBCC INDEXDEFRAG

"Yukon" dm_db_index_physical_stats

"Yukon" ALTER INDEX

Records (1)

How is data stored in SQL Server?

Example record types

Data

Index



PFS page

Pages (2)

Layout is the same for all page types
8k
96 byte header
slot array
records
free space

Extents

How are pages grouped?

Extents are:

A group of 8 contiguous pages

Start on an 8 page boundary

Tracked in IAM, GAM, SGAM pages
0 1 2 3 54 6 7

Heap (1)

Simplest storage arrangement

Comprised entirely of data pages


Non-clustered index (1)

A way to provide a different ordering

Define on heaps or clustered indexes

Leaf records contain RID of matching
record in base table

create index foo_nc on foo (last)

create index foo_nc on foo (last) include (first)

Non-clustered index (2)
P
L L
P
L L
P
L L
R
index leaf pages
index tree pages

Why use an index?

Allows a variety of access modes:

Singleton lookup

scan 3
starts

Readahead (1)

Why use readahead?

Keep the CPUs busy, maximize throughput

Feedback mechanism to determine how
far ahead of the scan point to read

Driven from parent level

Issues 1, 8, or 32 page IOs

Better contiguity = bigger IOs

Readahead (2)
L

What causes fragmentation (1)
Index leaf level of newly built index
Red arrow is the allocation order
Black arrows are following the logical order

What causes fragmentation (2)
Newly built index leaf after a single page split
Red arrow is the allocation order
Black arrows are following the logical order

Your tool for determining fragmentation

Keys to success are:

Knowing which indexes to look at

Knowing which options to use

Knowing how to interpret the results