Your browser (Internet Explorer 7 or lower) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.

X

SQL Server 2014 Managed Lock Priority for online index rebuild, PART 2

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.

SQL Server 2014 Managed Lock Priority for Online Index Rebuild, PART 1

As I already wrote in my post “Be careful with scheduling online index rebuilds” the problem with online index rebuild (except the need of huge amount of resources) is unpredictability and blocking. In my customer’s case the online index rebuild take between 2 and 12 hours. The new option in SQL Server 2014 called Managed Lock Priority seems to provide at least first steps in getting a solution to the problem (or creating additional problems?). The new feature allows a DBA to manage the S-lock/Sch-M lock for OIR.
As this new feature brings more questions than answers, it really grabbed my attention. So I decided to dive into it and to try to define when and how to use it.