Going to Pieces

Fragmentation, an indication that data isn't as compact and contiguous as it could be, comes in two general types: internal and external. Internal fragmentation involves how much free space is left on your pages and how compact the data is. External fragmentation involves how contiguous the pages are.

Excessive fragmentation isn't a concern in all cases. If your applications are randomly accessing individual rows of data, the number of rows on a page or how close one row is to other rows in the table doesn't matter. Accessing one row from a fragmented table is just as easy as from an unfragmented table. However, if your applications are performing ordered range scan operations and therefore reading all or most of the pages in a table, external fragmentation can greatly slow down the scan. The more contiguous and orderly the pages are, the better SQL Server can perform.

An interesting relationship exists between internal and external fragmentation in the leaf level of an index. In a clustered index, the leaf-level pages are the table's data pages. With internal fragmentation, space is available on the pages and potentially, there's room to insert new rows. If no internal fragmentation exists, the table has no space to insert new rows, and SQL Server will have to perform a page split and move some rows to another location before inserting new rows on a page. The chances are that the row's new location won't be contiguous to the original location, so the split operation will increase the amount of external fragmentation. So internal fragmentation in a table (or index) in which inserts are occurring can reduce the amount of external fragmentation, and lack of internal fragmentation can lead to increased external fragmentation.