What is the impact of temporarily setting ALLOW_PAGE_LOCKS ON
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.