The MSDN ALTER INDEX page says "An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF."
I'm modifying our index reorg/rebuild process to be sensitive to indexes that have
OFF. When I detect the index has the option turned off, I perform the following steps:
BEGIN TRANSACTION BEGIN TRY ALTER INDEX [MyIndex] ON [dbo].[MyTable] SET (ALLOW_PAGE_LOCKS = ON); ALTER INDEX [MyIndex] ON [dbo].[MyTable] REBUILD WITH ( SORT_IN_TEMPDB = ON , MAXDOP = 1 , PAD_INDEX = OFF , FILLFACTOR = 90 , ALLOW_PAGE_LOCKS = OFF , ALLOW_ROW_LOCKS = ON , IGNORE_DUP_KEY = OFF , DATA_COMPRESSION = NONE ); ALTER INDEX [MyIndex] ON [dbo].[MyTable] SET (ALLOW_PAGE_LOCKS = OFF); COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH
Presuming the database is not actually "in-use" at the time this rebuild process starts, does this pattern present any issues? Does setting
PAGE LOCKS ON cause any side-effects?
Note - the options, such as
FILLFACTOR = 90 are taken from the current definition of the index just prior to starting the reorg/rebuild process, and a decision is made whether to reorg or rebuild based on the number of pages in the index and the degree of fragmentation. This question is NOT about these settings being "good" or "bad", it is ONLY about the impact of temporarily turning
ALLOW_PAGE_LOCKS to `ON', to allow the index to be defragmented.
Also, I realize the
ALTER INDEX ... REBUILD statement has
ALLOW_PAGE_LOCKS = OFF, while the very next statement also turns it off... this is to allow the
ALTER INDEX ... REORG statement, which does not have any options present, to run properly, while also resetting the
ALLOW_PAGE_LOCKS setting off once it's completed.
I've wrapped the alter index statements in a transaction for only the indexes that have
ALLOW_PAGE_LOCKS OFF so that if anything goes wrong during the rebuild process the index is not modified.
Also, note, we are not using Ola Hallengren's solution due to corporate policy.
From @Shanky's comment, I've confirmed that
ALTER INDEX ... REBUILD does NOT require
ALLOW_PAGE_LOCKS ON; this option is only required when reorganizing an index. I've modified my code to do the transaction-wrapping shown above only when performing a reorganize where the index already has
ALLOW_PAGE_LOCKS turned off.