Index Fragmentation
Fragmentation can happen within a page (Internal) or between pages (External). SQL Server DBAs seem obsessed by rebuilding indexes.
You will probably detect and fix index fragmentation as part of your maintenance plan, but this article focuses on manually identifying and fixing index fragmentation.
Detecting Index Fragmentation
Note. Small indexes will often show large amounts of fragmentation, which might actually get worse after index rebuilds. Indexes with a page_count of less than ~1000 pages are unlikely to suffer from performance issues caused by fragmentation. Microsoft recommend avoiding rebuilds on indexes on indexes smaller than 1000 pages (here). Keep this in mind when looking at the information below.
The following query gives you the statistics for all indexes in every database. One of the columns produced is called avg_fragmentation_in_percent, which gives the information we are looking for.
SELECT * FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) ORDER BY avg_fragmentation_in_percent DESC
You can narrow this down to a specific database using the first parameter. We have also returned the object name associated with the object_id.
USE MyDatabase
GO
SELECT object_name(object_id) AS object_name, *
FROM sys.dm_db_index_physical_stats(DB_ID('MyDatabase'), NULL, NULL, NULL, NULL)
ORDER BY avg_fragmentation_in_percent DESC
We can now see which indexes are suffering from fragmentation.
Fixing Index Fragmentation
The following commands can be used to fix index fragmentation for all indexes on a specified table. A rebuild removes both internal and external fragmentation.
USE MyDatabase GO ALTER INDEX ALL ON dbo.MyTable REBUILD WITH (ONLINE = ON)
The WITH option of “ONLINE = ON” means transactions can continue against the object while the processing happens. Without it, the table would be locked for the duration of the operation.
A rebuild could result in the index being bigger as the index is rebuilt using the fill factor. If the fill factor is the default (100%), no space is left for future changes, which could result in a page split on the next insert. If you specify a fill factor, leaving some space for future updates you can reduce this fragmentation, but the index will be bigger. The necessity for a non-default fill factor depends on the nature of the work done against the table. The following example does a rebuild, specifying an 80% fill factor.
USE MyDatabase GO ALTER INDEX ALL ON dbo.MyTable REBUILD WITH (ONLINE = ON, FILLFACTOR = 80)
An alternative is to reorg the index, which shuffles the pages, only fixing external page fragmentation.
USE MyDatabase GO ALTER INDEX ALL ON dbo.MyTable REORG WITH (ONLINE = ON)
For more information see: