Improved Online Operations in SQL Server 2014

Online indexing rebuilding was a major breakthrough from SQL Server to support users re-indexing while the clustered index (or table) and other indexes are available during the index rebuild operation.   However, in real world there are multiple issues with these online operations as it does not have much flexibility. In SQL Server 2014, there are few options included for these online operations.

In SQL Server 2014, you have the option of setting a Lock Priority for online operations whereas in earlier versions, all operations were treated as having equal priority.

Let us do this with an example.  The below script will create the database and a table and also will populate a few thousand records for demonstration purposes.

SET NOCOUNT ON
USE master
GO
-- Check for databases existence & drop
IF EXISTS (SELECT 1 FROM sys.databases WHERE
name = 'LockPriorityTest')
DROP DATABASE LockPriorityTest
--Create the database
CREATE DATABASE LockPriorityTest
GO
USE LockPriorityTest
GO
-- Create a SampleTable table
CREATE TABLE SampleTable
(
ID INT IDENTITY  NOT NULL,
Name varchar(50),
CONSTRAINT pk_SampleTable PRIMARY KEY CLUSTERED (ID)
)
-- Create a unique index on the table
CREATE INDEX idx_Name ON SampleTable(Name)
GO
-- Insert few records
INSERT INTO SampleTable
(Name)
VALUES
('dbfriend')
GO 100000

Let us do a standard execution here and execute a table update inside a transaction.

        BEGIN TRAN
UPDATE [dbo].[SampleTable]
SET Name ='NewDB'
WHERE ID = 1000

While this is executing, we will run an online index rebuild in another session.

--Session 2
ALTER INDEX [idx_Name]
ON [dbo].[SampleTable]
 REBUILD
 WITH ( ONLINE = ON)

The following is the output for the both queries.

Note that alter index is blocked until the update is finished.

While these two are running, let us check the wait stats.

So, the alter index (session 62) is blocked by the UPDATE (session 60) with the wait type LCK_M_S.

Now let us use new feature in SQL Server 2014.

When the same update was running, the index rebuild was executed using the new feature in 2014.

        --Session 2
ALTER INDEX [idx_Name]
ON [dbo].[SampleTable]
 REBUILD
WITH ( ONLINE = ON
              (
              WAIT_AT_LOW_PRIORITY
                     (
                     MAX_DURATION = 1,
ABORT_AFTER_WAIT = SELF
                     )
              )
       )

In the above statement, WAIT_AT_LOW_PRIORITY is set with two options. MAX_DURATION is set to 1 (in minutes). If MAX_DURATION is not specified then it will be set to 0 minutes.

There are three options for ABORT_AFTER_WAIT, which are NONE, SELF and BLOCKERS.

NONE is the default option which will be same as it in previous versions of SQL Server. Therefore omitting the WAIT_AT_LOW_PRIORITY option is equivalent to WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

The SELF option will terminate the current session. So in this example, alter index operation will be terminated as shown in the following image.

BLOCKERS will terminate the other blocking operations. So in this case, if BLOCKERS parameter was used the Update statement will be terminated.

Let us look at the wait type for the alter index session before it was terminated.

Note the new wait type which is introduced in SQL Server 2014. Similarly, a few other wait types are introduced to support different lock types.

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

DBAs can utilize WAIT_AT_LOW_PRIORITY option can be utilized improve the flexibility of online operations.

]]>