(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan, where you are getting week by week via email all the essential knowledge you need to know about performance tuning on SQL Server.)

In todays blog posting I want to talk a little bit about Online Index Rebuild operations, and how they are improved in SQL Server 2014. As you know, SQL Server 2005 first introduced Online Index Rebuild operations. But these online operations are not really online operations, because SQL Server has to acquire a Shared Table Lock (S) at the beginning of the operation, and a Schema Modification Lock (Sch-M) on the corresponding table at the end of the operation. So it’s just a marketing trick that these operations are really online operations. But hey, “online” always sounds better than “partially online”

Nonetheless SQL Server 2014 has some improvements on how we can control the blocking situation that can occur at the beginning and at the end of an Online Index Rebuild operation. For that reason you are now able to define a so-called Lock Priority when executing an Online Index Rebuild. Just have a look on the following statement where you can see this new syntax in action:

ALTER INDEX idx_Col1 ON Foo REBUILD
WITH
(
   ONLINE = ON
   (
      WAIT_AT_LOW_PRIORITY 
      (
         MAX_DURATION = 1, 
         ABORT_AFTER_WAIT = SELF
      )
   )
) 
GO

You can define with the keyword WAIT_AT_LOW_PRIORITY what happens when a blocking situation occurs. With the first property MAX_DURATION you specify how long you want to wait – the unit is here minutes, *NOT* seconds! And with the property ABORT_AFTER_WAIT you specify which session should be rolled back by SQL Server. SELF means that the ALTER INDEX REBUILD statement is just rolled back, and when you specify BLOCKERS the blocking session is rolled back. Of course, the Online Index Rebuild operation is started immediately when no blocking occurs. So you are only configuring here what happens when a blocking situation occurs!

So let’s have a look on this in action. Let’s create a database, a simple table, and a Clustered Index.

-- Creates a new database
CREATE DATABASE Test
GO
-- Use the database
USE Test
GO
-- Create a simple table
CREATE TABLE Foo
(
Col1 INT IDENTITY(1, 1) NOT NULL,
Col2 INT NOT NULL,
Col3 INT NOT NULL
)
GO
-- Create a unique Clustered Index on the table
CREATE UNIQUE CLUSTERED INDEX idx_Col1 ON Foo(Col1)
GO
-- Insert a few test records
INSERT INTO Foo VALUES (1, 1), (2, 2), (3, 3)
GO

To trigger a blocking scenario I’m just starting a new transaction in a different session without committing it:

BEGIN TRANSACTION
UPDATE Foo SET Col2 = 2
WHERE Col1 = 1

This means that we are acquiring an Exclusive Lock (X) on the record that we are changing, an Intent-Exclusive Lock (IX) on the corresponding page, and also an Intent-Exclusive Lock (IX) on the table itself. We just have created the traditional locking hierarchy in SQL Server: Table => Page => Record. And the IX Lock on the table level is incompatible with the Shared Lock that the Online Index Rebuild operation will try to acquire – a traditional Locking/Blocking scenario will happen. When you are now just running a “traditional” Online Index Rebuild operation, it will block:

ALTER INDEX idx_Col1 ON Foo REBUILD
WITH
(
   ONLINE = ON
)
GO

When you look into the Dynamic Management View sys.dm_tran_locks you can see that the session tries to acquire a Shared Lock (S) but has to wait. The query will just wait forever. As I have said earlier: “partially online”…

The interesting thing is now what happens when we execute the Online Index Rebuild operation with a Lock Priority:

-- Perform an Online Index Rebuild
ALTER INDEX idx_Col1 ON Foo REBUILD
WITH
(
   ONLINE = ON
   (
      WAIT_AT_LOW_PRIORITY 
      (
         MAX_DURATION = 1, 
         ABORT_AFTER_WAIT = SELF
      )
   )
) 
GO

In this case our ALTER INDEX statement will wait for 1 minute (MAX_DURATION), and afterwards the statement itself is cancelled (ABORT_AFTER_WAIT). If you specify here the option BLOCKERS, then the blocking session is rolled back. But you will encounter interesting things when we look in the mean time (during the 1 minute duration) into the DMV sys.dm_tran_locks:

As you can see from the screenshot, SQL Server reports here a request status of LOW_PRIORTY_WAIT. So the 3 possible request status (GRANT, WAIT, CONVERT) have now a 4rd option: LOW_PRIORITY_WAIT. Things are also getting more interesting, when we look into the DMV sys.dm_os_waiting_tasks:

The waiting session of the Online Index Rebuild reports the new wait type LCK_M_S_LOW_PRIORITY. This means that we can see from the wait statistics on the server level (sys.dm_os_wait_stats) when Online Index rebuild operations were blocked – nice. But LCK_M_S_LOW_PRIORITY is not the only new wait type. When you look into the DMV sys.dm_os_wait_stats you can see 21 new wait types that are dealing with Lock Priorities in SQL Server 2014:

• 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 the major locking wait types (LCK_M_*) have an additional Lock Priority wait type. That’s really cool and powerful, because you can now track down very easily why an Online Index Rebuild operation was blocked. And in addition SQL Server applies the same technique (Lock Priorities) to Partition Switching, because that operation needs a Schema Modification Lock (Sch-M) on both tables (source table, target table) during the switch.

I hope that I have given you with this blog posting a good introduction to Lock Priorities in SQL Server 2014, and why “online” operations in SQL Server are only “partially online”.

Thanks for reading

-Klaus