Can I have predictable Online Index Rebuilds (cont.)
The Managed Lock Priority in SQL Server 2014 give us a choice for managing locks of the online index rebuilt operation.
We need to do that because the online index rebuild operation is not performed entirely online. There are Schema locks that the process need to obtain at the beginning and at the end of the index rebuild and those type of locks (especially Sch-M one) could have incompatibility with other locks currently obtained on the object. Because of that some parts of the process need to be done in “offline” mode, and as a result we experience blocking.
In this part II of my blog post I am continuing to describe the Managed Lock Priority OIR with the second new option we have. What happens when we give a priority to a user transaction instead of the online index rebuild process.