How SQL Server 2014 promotes online operations that are not online

In today's article, I would like to talk about the online index Rebuild operations, and how they have been promoted in SQL Server 2014. As we all know, the online index rebuild operation has been introduced since SQL Server 2005. However, these online operations are not really online, because SQL Server needs to obtain a shared table lock at the beginning of the operation, which requires a schema modification lock on the corresponding table at the end of the operation (schema modification lock ( SCH-M)). So these operations are really online operations, just marketing tips (marketing trick). But, pro, "online" is certainly better than "part-on-the-line".

Still , SQL Server 2014 has made some improvements in blocking the start and end of online index rebuilds. So, when you perform an online index rebuild, you can define the so-called lock priority. take a look at the following code and you'll see the new syntax that works:

1 ALTER INDEXIdx_col1 onFoo REBUILD2  with3 (4ONLINE=  on5    (6 wait_at_low_priority7       (8Max_duration= 1, 9Abort_after_wait= SelfTen       ) One    ) A )  - GO

When a blocking situation occurs, you can define how to handle it with the wait_at_low_priority keyword. Use the 1th attribute max_duration To specify the time you want to wait-here is the minute, not the second! With the abort_after_wait property, you specify which session needs to be rolled back by SQL Server. Self means that the ALTER INDEX rebuild statement is rolled back and the blocked session is rolled back when you specify blockers . Of course, when no blocking occurs, the lead reconstruction operation is performed immediately. So here you can only configure what to do when the blocking situation occurs.

All right, let's do the real exercise. We create a new database, a simple table and a clustered index.

1 --creates a new database2 CREATE DATABASETest3 GO4 5 --Use the database6  UseTest7 GO8 9 --Create a simple tableTen CREATE TABLEFoo One ( ACol1INT IDENTITY(1,1) not NULL, -Col2INT  not NULL, -Col3INT  not NULL the ) - GO -  - --Create a unique Clustered Index on the table + CREATE UNIQUE CLUSTERED INDEXIdx_col1 onFoo (Col1) - GO +  A --Insert a few test records at INSERT  intoFooVALUES(1,1), (2,2), (3,3) - GO

In order to trigger blocking, I start a new transaction in a different session, but do not commit:

 1  begin  transaction  2   UPDATE  Foo set  Col2 =  2  4  where  Col1 
   
    =  
    1  
   

This means that we get an exclusive lock (Exclusive Lock (X))on the record that needs to be modified, and we get the Intent exclusive lock on the corresponding page (intent-exclusive Lock (IX)) , the intent-exclusive lock ( IX)is obtained by the intent of the table itself. We have just created a typical locking hierarchy (locking hierarchy) in SQL Server: Table = = page and record. The shared lock required for the intent exclusive lock (IX Lock) and the online index rebuild operation at the table level is incompatible-a typical lock/block scenario has occurred. When you now perform an online index rebuild operation, blocking occurs:

1 ALTER INDEX  on Foo REBUILD 2  with 3 (4    =  on 5 )6GO

When you look at the DMV sys.dm_tran_locks, you'll see that the session that needs a shared lock (S) needs to wait. This session will wait forever. I just said, "part of the line" ...

1 SELECT *  from    Sys.dm_tran_locks

When we perform an on-line index rebuild with lock priority, the interesting thing happens:  

1 --Perform an Online Index Rebuild2 ALTER INDEXIdx_col1 onFoo REBUILD3  with4 (5ONLINE=  on6    (7 wait_at_low_priority8       (9Max_duration= 1, TenAbort_after_wait= Self One       ) A    ) - )  - GO

In this case, our ALTER INDEX statement waits 1 minutes (max_duration) and then the statement itself is canceled (abort_after_wait).

If you specify the blockers option here, the blocked session will be rolled back. When we looked at the DMV sys.dm_tran_locksat the same time (during the 1-minute period), we saw something interesting:

As you can see, SQL Server is requesting a low_priority_wait state here. So 3 request states (Grant,wait,convert) have the 4th option:low_priority_wait. When we look at the DMV sys.dm_os_waiting_tasks , things get interesting (59 is the session ID of the execution statement):

1 SELECT *  from WHERE session_id=' a '

A wait session in the lead rebuild operation reported a new wait type of lck_m_s_low_priority. This means that when the online index rebuild operation is blocked, we can get it from the waiting statistics at the server level (sys.dm_os_wait_stats)-yes!

But lck_m_s_low_priority is not a new wait type. In SQL Server 2014, when you view the DMV sys.dm_os_wait_stats , you will see 21 new wait types:

1 SELECT *  from WHERE  like ' %low_priority% '

• Lck_m_sch_s_low_priority
• Lck_m_sch_m_low_priority
• Lck_m_s_low_priority
• Lck_m_u_low_priority
• Lck_m_x_low_priority
• Lck_m_is_low_priority
• Lck_m_iu_low_priority
• Lck_m_ix_low_priority
• Lck_m_siu_low_priority
• Lck_m_six_low_priority
• Lck_m_uix_low_priority
• Lck_m_bu_low_priority
• Lck_m_rs_s_low_priority
• Lck_m_rs_u_low_priority
• Lck_m_rin_nl_low_priority
• Lck_m_rin_s_low_priority
• Lck_m_rin_u_low_priority
• Lck_m_rin_x_low_priority
• Lck_m_rx_s_low_priority
• Lck_m_rx_u_low_priority
• Lck_m_rx_x_low_priority

All major wait types (lck_m_*) have additional lock-priority wait types. This is very cool and very powerful, because you can easily track why the online rebuild index operation is blocked. In addition, the same technique is applied for partition switching (Partition switching) (lock priorities) because during the switchover, the operation also acquires a schema modification lock on the 2 tables (the original table, the target table) (schema Modification Lock (sch-m)).

I hope this article will allow you to understand the lock priority (lock priorities) in SQL Server 2014 and why the "online" operation in SQL Server is actually "partially online".

Thanks for your attention!

How SQL Server 2014 promotes online operations that are not online