thinQmaster Virtual testing partner Adaptive practice exams that find your weak spots and drill them. Every answer explained. From $69
Enroll

thinQmaster

Find your exam

Five questions free, before you decide.

Step 1 · choose a vendor

In one of our camps? Your practice exams come with the tuition — they are on your study page already.

Data & Analytics · Advanced

Index Fragmentation

Disorder in index pages, either logical (pages out of order) or low page density, which reduces scan efficiency.

Definition

Fragmentation grows with inserts, updates and deletes that cause page splits. Common SQL Server guidance is to REORGANIZE at moderate fragmentation (roughly 5-30%) and REBUILD above that, although on modern SSD storage, page density and statistics often matter more.

Example

ALTER INDEX ALL ON Sales REBUILD WITH (ONLINE = ON);

Exam tip

REORGANIZE is always online and lightweight; REBUILD recreates the index and also updates its statistics.

Related terms