Rebuild – this time it fragmented even more

You do your homework, check which index are fragmented and rebuild them. Thinking all is well. Then just to be sure you run the check again to see how much better it got. Surprisingly, the frag % is even bigger!

My story: I had an index with 80% fragmentation and ran a rebuild index with ALTER INDEX REBUILD. With no other access to the table as far as I know, the fragmentation % went UP to 83%. Tried again – next run got it down to 50%, not good enough. But I felt I was on to something so I ran it again. Now up to 86% etc. That happened to depend on 1) having set maxdop <>1 (it was set to 2 at the time on that SQL 2005 instance) and 2) “allow_page_locks = OFF” (actually that was an earlier mistake by a developer so we could easily change that to = ON and then it started to work again.

What SQL Server does is that it spreads out the rebuild of the index to multiple threads and each thread writes in different parts. The result is a couple of individually defragmented parts but in the whole it is still fragmented on disk. We could also have solved it by setting maxdop=1 but in this case we wanted to have multiple threads rebuilding it. Not defragging it was not an option, of course. Do you have your indecies under the microscope? If not you could contact us , not if, but when, you are starting to have performance problems.

Update: Here is result from the support case the company took on that issue, provided by my Irish colleague at the site Steven Devaney (thank you, Steven!). SQL Server 2005 & 2008 Enterprise Edition Online index rebuild of a non-clustered index can result in increased fragmentation when: Using MaxDOP <> 1 (if you do not specify this the rebuild uses the value for the server – see sp_configure) Index is set to ALLOW_PAGE_LOCK = 0 (disallow page locking). This is because the multiple processes used to rebuild the index have no means of consolidating their work so when their seperate result sets are merges as the fininshed index it is fragmented. Fixing this with a sort afterwards would introduce a very large delay in rebuilding the index.

Recommendations: Only disallow page locking if you really need to. To see if any indicies are set to disallow page locking: select object_name(object_id), name from sys.indexes where allow_page_lock = 0 Review the list and use ALTER INDEX to change if necessary. Workaround: If you need to rebuild such an index online and it must remain disallowing page locks then you should rebuild with MAXDOP = 1. Fix: I raised an incident with Microsoft and they are proposing it as a bug-fix for SQL Server 2011.