SQL Server Index Myth 3: Online Index Operations Don’t Block

One of the advantages of SQL Server Enterprise Edition is the ability to build indexes online. During an online index build, the table on which the index is being created will still be available for queries and data modifications. This feature can be extremely useful when a database needs to be accessed and maintenance windows are short to nonexistent.

A common myth with online index rebuilds is that they don’t cause any blocking. Of course, like many myths, this one is false. When using an online index operation, there is an intent shared lock held on the table for the main portion of the build. At the finish, either a shared lock, for a nonclustered index, or a schema modification lock, for a clustered index, is held for a short time while the operation moves in the updated index. This differs from an offline index build where the shared or schema modification lock is held for the duration of the index build.

Of course, you will want to see this in action; to accomplish this, you will create a table and use Extended Events to monitor the locks that are applied to the table while creating indexes with and without the ONLINE options. To start this demo, execute the code in Listing 6. This script creates the table dbo.MythThree and populates it with ten million records. The last item it returns is the object_id for the table, which is needed for the subsequent parts of the demo. For this example, the object_id for dbo.MythThree is 1319675749.

USE AdventureWorks2014
GO
CREATE TABLE dbo.MythThree
(
RowID int NOT NULL,Column1 uniqueidentifier
);
WITH L1(z) AS (SELECT 0 UNION ALL SELECT 0),
L2(z) AS (SELECT 0 FROM L1 a CROSS JOIN L1 b),
L3(z) AS (SELECT 0 FROM L2 a CROSS JOIN L2 b),
L4(z) AS (SELECT 0 FROM L3 a CROSS JOIN L3 b),
L5(z) AS (SELECT 0 FROM L4 a CROSS JOIN L4 b),
L6(z) AS (SELECT TOP 10000000 0 FROM L5 a CROSS JOIN L5 b)

INSERT INTO dbo.MythThree

SELECT ROW_NUMBER() OVER (ORDER BY z) AS RowID, NEWID()
FROM L6;
GO
SELECT OBJECT_ID('dbo.MythThree')
GO

To monitor those events in this scenario, you’ll use Extended Events to capture the lock_acquired and lock_released events fired during index creation. Open sessions in SSMS for the code in Listing 7 and Listing 8. Use the session_id from Listing 8 for the session_id in Listing 7; for this scenario, the session_id is 42. After the Extended Events session is running, you can use the live view to monitor the locks as they occur.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name = 'MythThreeXevents')
DROP EVENT SESSION [MythThreeXevents] ON SERVER
GO
CREATE EVENT SESSION [MythThreeXevents] ON SERVER
ADD EVENT sqlserver.lock_acquired(SET collect_database_name=(1)
WHERE [sqlserver].[session_id]=(42) AND [object_id]=(1319675749)),
ADD EVENT sqlserver.lock_released(WHERE [sqlserver].[session_id]=(42) AND [object_id]=(1319675749)
ADD TARGET package0.ring_buffer
GO
ALTER EVENT SESSION [MythThreeXevents] ON SERVER STATE = START
GO

In the example from Listing 8, creating the index with the ONLINE option causes the lock acquired and the released events shown in Figure 2. In the output, the SCH_S (Schema_Shared) lock is held from the beginning of the build to the end. The S (Shared) locks are held only for a few milliseconds at the beginning and ending of the index build. For the time between the S locks, the indexes are fully available and ready for use.

USE AdventureWorks2014
GO
CREATE INDEX IX_MythThree_ONLINE ON MythThree (Column1) WITH (ONLINE = ON);
GO
CREATE INDEX IX_MythThree ON MythThree (Column1);
GO