How to control online Index Rebuild Locking using SQL Server 2014 Managed Lock Priority
Ahmad Yaseen
Big Data Support Escalation Engineer at Microsoft | Certified Trainer | Author | Blogger | Data Management Expert | Data Consultant/Architect
When you perform a SQL Server Online Index Rebuild operation, introduced for the first time in SQL Server 2005, the index will not be taken down. But at a specific point, in which the new index new is built and switched from the old structure of the index, a special kind of lock, Schema Modification (SCH-M), will be granted. This lock may cause blocking if your database server is busy.
The SCH-M lock is used for table structure change DDL operations. In these cases, SQL Server will wait until the index rebuild process release that lock, as all operations have the same priority, which may take a long time for large and/or high throughput tables.
On the other hand, another type of locking, called the Schema Stability Lock (SCH-S), is used by any DML T-SQL query that reads or modifies table data to make sure that the structure of the table that the query is using will not be changed until the query is finished and the SCH-S lock is released. In other words, you cannot perform an online index rebuild on a table with queries running on it that have a SCH-S lock granted on the table, which effectively prevents you from changing the table’s schema mid-query. Also, you cannot change or read data from a table with SCH-M lock type during an online index rebuild operation.
Prior to SQL Server 2014, there were two main queues that were maintained to manage locks; Grant and Waitqueues. When a lock is requested and can be handled, it will be allocated into the grant queue then executed, otherwise, it will wait in the wait queue with all other processes in that queue with the same priority. In SQL Server 2014, new functionality was introduced that allows you to control how the blocking mechanism, that is required by the online index rebuild operation, is handled. The new functionality is called Managed Lock Priority. This functionality benefits from the newly defined Low-Priority queue that contains the processes with priorities lower than the ones waiting in the wait queue, giving the database administrator the ability to manage the waiting priorities.
Read More:How to control online Index Rebuild Locking using SQL Server 2014 Managed Lock Priority