What is fragmentation?

It’s common knowledge that SQL indexes become fragmented over time and should be rebuilt to remove that fragmentation. But what exactly is fragmentation?

First, a little bit on index structure.

The index key defines the logical order of the index leaf pages. Each page has two pointers, one to the previous page and one to the next page in the index.

In an ideal situation, the logical order of the pages would correspond to the physical order, and a page with higher index key values  would have a higher page number (corresponding to physical position in the file) than a page with lower index key values.

If that is the case for all pages in the index leaf level, then the index has no fragmentation. Unfortunatly, that is not normally the case. Inserts and updates to the index can cause page splits. A page split moves half the rows on one of the index leaf pages to a new page and then linkes that page into the chain. It’s unlikely that a free page will be in the correct physical logation in the file and hence after the page split, the physical and logical ordering of the pages will no longer be the same.

The logical fragmentation is a measure of how different the logical and physical orderings are. It’s a ratio of the number of pages found out of order (lower in the file than the previous page in the index) to the total number of pages in the index.

As an example, consider an index with 1000 pages in it. While traversing the index leaf level, 400 of those pages are found at a physical location lower than the page before them in the index. Hence the ratio of out of order pages to total pages is 400:1000 and we can say that the index is 40% fragmented.

Because fragmentation is purely a feature of where the pages are on disk, it only affects SQL when the table/index in question is not in memory and has to be read from disk. For this reason, it’s not usually necessary to worry about the fragmentation of tables with very few pages (<100) as they will probably remain in memory if they are heavily used.

On larger tables the presence of fragmentation at the leaf level will make scans of the index less efficient, requiring more IOs and disk time to get the data off disk. If the system already has an IO bottleneck, this can cause severe problems.

Detecting fragmentation

There are two ways to see the fragmentation of an index. The first is the older SQL 2000 way. It still works in SQL 2005 and 2008, but is deprecated in both versions and will be removed from a future version

DBCC SHOWCONTIG

Showcontig can display the fragmentation results for a single index, for all the indexes on a table or for all of the indexes in the database, depending on the options passed to it. It also can return results as a recordset, instead of text, which is very usweful for automated index maintenance

[source:SQL]
DBCC SHOWCONTIG (<Table Name>) — just for this table
DBCC SHOWCONTIG (<Table Name>, <Index Name>) — just for this index
DBCC SHOWCONTIG WITH ALL_INDEXES — everything in the DB
DBCC SHOWCONTIG WITH TABLERESULTS — results in a recordset
[/source]

sys.dm_db_index_physical_stats

This is one of the DMVs introduced with SQL 2005 and it’s the replacement for showcontig. It takes 5 parameters, the database ID, the table, the index, the partition and a setting for how detailed the results should be.

[source:SQL]SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) — all indexes on all tables in all databases
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL); — all indexes in this DB
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(<Table Name>), NULL, NULL, NULL); — all indexes on that table
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(<Table Name>), <Index ID>, NULL, NULL) — stats for just that index[/source]