3

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 ALLOW_PAGE_LOCKS turned 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.

EDIT:

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.

|improve this question
  • 1
    You are talking about reorganize or rebuild ? In reorganize Page lock is required as it does page movement while in rebuild page lock is not necessarily required. Allowing page lock on in reorganize will not have effect as a fact you cannot reorganize without page locks on – Shanky Apr 20 '16 at 15:05
  • SAP has some tables that have the ALLOW_PAGE_LOCKS = OFF set, which hampered our maintenance solution, so we just added these tables to the EXCEPTIONS list. :-) – hot2use Apr 20 '16 at 15:08
  • Are you on Enterprise Edition? If so, you might be able to avoid this if you REBUILD ONLINE. That only takes a schema lock. – Arthur D Apr 20 '16 at 15:18
  • thanks for clarifying that point, @Shanky - I now have my code only modifying the page locks setting for reorgs. – Max Vernon Apr 20 '16 at 15:23
  • @ArthurDaniels - yes it is Enterprise Edition, and I am using the online=ON option. – Max Vernon Apr 20 '16 at 15:24
2

Presuming the database is not actually "in-use" at the time this rebuild process starts

Assuming what you are presuming, I don't what side-effect it could have, outside of the obvious allowing for index REORG to happen :-).

Of course, I have no proof, but not sure what proof to look for that nothing bad will happen when nothing is happening in the DB outside of this REORG operation.

I am also not aware of there being any analogy here to Foreign Keys being untrusted, and hence not considered by the Query Optimizer, if created using the WITH NOCHECK option to skip validation (and not trusted until altering using WITH CHECK).

Regarding the notion of index property vs structure of the index pages: I would say that this could be answered by altering a large index to just turn this option ON and OFF. If it happens instantly, then it is not writing the change to all pages. And, it is also probably a moot point since even if there is a change, it will be handled via the ALTER INDEX [MyIndex] ON [dbo].[MyTable] SET (ALLOW_PAGE_LOCKS = OFF); and completed before the COMMIT. Still, I can't imagine this being anything other than a simple index property, since it can't change on a per-page basis. Of course, this can also be seen by using DBCC PAGE to view the contents of an index before and after you make this change.

|improve this answer
  • 1
    that's pretty much what I thought, I guess I'm wondering if changing the option simply changes a bit in the index header, or if it actually changes the structure of the pages in some way. – Max Vernon Apr 20 '16 at 15:10

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.