Rebuilding an index can result in fragmented index when ALLOW_PAGE_LOCKS option is not enabled

Applies to: Microsoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise EditionMicrosoft SQL Server 2005 Enterprise X64 Edition More

Symptoms


When you perform an Index rebuild operation on a multicore machine, it might result in a fragmented index if ALLOW_PAGE_LOCKS option is disabled during the rebuild operation.

Cause


This behavior is by design.

Resolution


Enable page locks optionat index level using the following t-sql statement:
ALTER INDEX [index_name] ON [table_name] SET ( ALLOW_PAGE_LOCKS  = ON ) 




Another option would be to execute the rebuild index command using MAXDOP = 1 query hint so that the index is not rebuilt using parallelism.