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

Se aplica a: Microsoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise EditionMicrosoft SQL Server 2005 Enterprise X64 Edition


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.


This behavior is by design.


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.