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 Wait queues. 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.
To use the low priority lock, you need to implement it in ALTER INDEX and ALTER TABLE statements used for the online index rebuild and partition switch operations.
The definition of the low priority wait consists of three parts;
- the WAIT_AT_LOW_PRIORITY option keyword,
- the MAX_DURATION property that defines how long, in minutes, the command will wait
- and the ABORT_AFTER_WAIT property that specifies the session that will be rolled back after the wait time expired.
The ABORT_AFTER_WAIT parameter comes with three options:
- NONE: The online index rebuild operation will be returned to the wait queue after the wait time is expired, changing the low priority wait into normal wait priority. This will work same as previous SQL Server versions. This option helps in giving other queries time, equal to MAX_DURATION, to proceed without any blocking.
- SELF: The online index rebuild operation will be rolled back if it is not completed after specific time equal to MAX_DURATION, giving the priority to other user actions instead of the online index rebuild operation.
- BLOCKERS: All transactions that are blocking the online index rebuild operation will be killed if the blocking still occurring after expiring the MAX_DURATION time. So that you can easily rebuild the index.
If you do not specify the WAIT_AT_LOW_PRIORITY option keyword in the online index rebuild statement, SQL Server will set the MAX_DURATION property to 0 and the ABORT_AFTER_WAIT property to NONE, working same as the previous SQL Server versions. Take into consideration that the WAIT_AT_LOW_PRIORITY option specifies what will happen only when a blocking situation is occur. If you look into the sys.dm_os_wait_stats DMV, you will find 21 new wait types related to the SQL Server 2014 Lock Priorities. The below T-SQL statement can be used to retrieve these wait types:
|
1 2 3 |
SELECT wait_type FROM sys.dm_os_wait_stats WHERE wait_type LIKE '%_LOW_PRIORITY' |
The Lock Priority related (LCK_M) wait types will be as shown below:
Let us see how we can use the new Lock Priority feature to control the locking process. We will start by creating a simple table with clustered index on the Primary Key and one non-clustered index using the T-SQL script below:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE SQLShackDemo GO CREATE TABLE LockPriorityTest ( ID INT IDENTITY (1,1) PRIMARY KEY, EmpName NVARCHAR (50), EmpPhone NVARCHAR (50), EmpAddress NVARCHAR (MAX) ) GO CREATE NONCLUSTERED INDEX IX_LockPriorityTest_EmpName ON [LockPriorityTest] (EmpName,EmpPhone) |
After creating the table, we will fill that table with 1 million records using ApexSQL Generate tool, a SQL Server test data generator:
Now the table is created and filled with the testing data. We will run two queries, the first one is a SELECT query from the created table executed within a transaction and locking the table for one minute and half then it will be rolled back. This is achieved by running the below T-SQL script under session number 54:
At the same time, we will perform an online index rebuild operation on the same table to rebuild the non-clustered index after inserting the large batch of records. This is also achieved using the below normal ALTER INDEX query with no extra new options running under session number 57:
You can see clearly that the ALTER INDEX statement is blocked until the previous SELECT statement that is locking the table is finished. Let us take a chance of this blocking happening, while the two queries are running, to check the wait status of these two queries using the SP_WHOISACTIVE statement. The result will show us that the ALTER INDEX statement running under session 57 is blocked by the SELECT statement running under session 54 as shown below:
What is happening internally is that, the first SELECT query is running against the table, with a SCH-S lock granted to it on that testing table. When the online ALTER INDEX T-SQL command is issued, it asks for SCH-M lock on the same table that is already locked with the SCH-S lock granted to session number 54. So, the SCH-M lock request will get into the wait queue until the SCH-S lock is released.
Any SELECT or UPDATE query issued on that table after that will ask for the SCH-S lock and get into the wait queue too waiting its turn. When the SCH-S lock that is granted to the first SELECT query on the table is released, the SCH-M lock will be granted to the online ALTER INDEX query on the same table, so that the online index operation will be processed. After completing the online index rebuild operation, all queries in the wait list will be processed after being granted the SCH-S lock on the table, continuing the normal operations on the table. This is the ideal scenario, but you can imagine a situation with queries taking a long time running against a table or rebuilding the indexes for huge tables.
Let us see how this situation will differ in the context that includes the SQL Server 2014 Low Priority option. To be able to use the low priority option, the WAIT_AT_LOW_PRIORITY keyword will be added and provided with two parameters, MAX_DURATION and ABORT_AFTER_WAIT, as mentioned previously. We will study how we can manage the lock priority using the different ABORT_AFTER_WAIT options.
ABORT_AFTER_WAIT = NONE
When we set the ABORT_AFTER_WAIT parameter of the WAIT_AT_LOW_PRIORITY ALTER INDEX option to NONE, using the ALTER INDEX T-SQL statement shown below:
|
1 2 3 4 5 6 7 8 |
USE SQLShackDemo GO ALTER INDEX IX_LockPriorityTest_EmpName ON dbo.[LockPriorityTest] REBUILD WITH (FILLFACTOR = 90, ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE ) )); |
The result will show that SQL Server will act the same way we saw in the previous example and the SQL Server versions prior to SQL Server 2014 after the MAX_DURATION time is expired, where the online index rebuild operation will be returned to the wait queue after the wait time elapsed. With the MAX_DURATION parameter value provided equal to zero, the online index rebuild will wait in the wait queue with no change from the normal online index rebuild operation, as shown below:
ABORT_AFTER_WAIT = SELF
Setting the ABORT_AFTER_WAIT parameter of the WAIT_AT_LOW_PRIORITY ALTER INDEX option to SELF, using the following ALTER INDEX T-SQL statement:
|
1 2 3 4 5 6 7 8 |
USE SQLShackDemo GO ALTER INDEX IX_LockPriorityTest_EmpName ON dbo.[LockPriorityTest] REBUILD WITH (FILLFACTOR = 90, ONLINE = ON( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF) )); |
The result shows that the ALTER statement is blocked by session 54 again and waiting to be granted a new lock type, the LCK_M_XX_OW_PRIORITY lock, in the Low Priority queue, as shown below:
It will wait for the MAX_DURATION time to be expired, which is 1 minute in our example. If the blocking query is not finished, the ALTER INDEX statement will terminate itself and rolled back, leaving the priority to the other queries to be executed, generating the “Lock request time out period exceeded” error shown below:
ABORT_AFTER_WAIT = BLOCKERS
The last scenario when we set the ABORT_AFTER_WAIT parameter of the WAIT_AT_LOW_PRIORITY ALTER INDEX option to BLOCKERS, using the following ALTER INDEX T-SQL statement:
|
1 2 3 4 5 6 7 8 |
USE SQLShackDemo GO ALTER INDEX IX_LockPriorityTest_EmpName ON dbo.[LockPriorityTest] REBUILD WITH (FILLFACTOR = 90, ONLINE = ON( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS) )); |
The result shows that the ALTER statement that is blocked by session number 54 is waiting to be granted the LCK_M_XX_OW_PRIORITY lock, in the Low Priority queue, as shown below:
In addition, it will wait for the MAX_DURATION time to be expired, which is 1 minute as specified in the query. If the blocking query is not finished in one minute, the ALTER INDEX statement will kill all blocking transactions, which is session number 54 in our example, showing that the session is killed due to a high priority DDL operation, as you can see from the below error message:
On the other side, the online index rebuild operation will be completed successfully in short time as all blockers are killed:
Conclusion
SQL Server 2014 comes with a new lock queue that helps in managing the locking process more efficiently. The Low Priority option allows us to control the blocking that is required for the online index rebuild operation. Within this article, we described the different options for the low priority option and how the online index rebuild operation behaves with these options.
Each option described fits a specific environment in which you decide to give priority to the online index rebuild operation DDL statement over the other queries running against a database table or give it the chance to run within a specified time then stopped to give the chance to the other DML queries.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021