DBCC INDEXDEFRAG doesn't seem to work like it should.

Question

I have a very large table, over 30 million records, that has a logical fragmentation of about 60%, and this is even if I run DBCC INDEXDEFRAG on it. Is this normal, and if not, what can I do about reducing the fragmentation?

Answer

In SQL Server 2000, Microsoft introduced DBCC INDEXDEFRAG to help reduce logical disk fragmentation. When this command runs, it reduces fragmentation and does not lock the table, allowing other users to access the table when the defragmentation process is running. Unfortunately, this command doesn't do a great job of logical defragmentation, as you have noticed.

The only way to truly reduce logical fragmentation is to rebuild your table's indexes. While this will eliminate fragmentation, unfortunately it will lock each table at the indexes for the table are being reindexed, preventing users from accessing it during this process. This means that you will need to find a time when this will not present a problem to your users. And at 30 million rows, this could take a while, depending on the quantity and width of your indexes.

When you rebuild your indexes on this table, you will also want to select an appropriate fillfactor in order to prevent unnecessary page splits. The amount of the fillfactor will depend on how read or write intensive your table is. The more write intensive the table is, the greater the fill factor needs to be.

Also, for the long term, you will also want to consider scheduling an index rebuild periodically, such as once a week or once a month in order to prevent such sever logical fragmentation from happening again.