Why does CREATE INDEX … WITH ONLINE=ON block access to the table over a period of minutes?

Question

I have an existing table:

CREATE TABLE dbo.ProofDetails
(
    ProofDetailsID int NOT NULL 
        CONSTRAINT PK_ProofDetails 
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , ProofID int NULL
    , IDShownToUser int NULL
    , UserViewedDetails bit NOT NULL 
        CONSTRAINT DF_ProofDetails_UserViewedDetails 
        DEFAULT ((0))
);

This table has 150,000,000 rows. The system is in operation 24x7x365, so there are no regularly occurring maintenance windows.

I want to add an index to the table, and with the Enterprise edition of SQL Server, I should be able to do that without blocking write access to the table. The command I used was:

CREATE INDEX IX_ProofDetails_ProofID_Etc 
ON dbo.ProofDetails (ProofID, IDShownToUser)
INCLUDE (UserViewedDetails)
WITH (ONLINE=ON
    , ALLOW_ROW_LOCKS=ON
    , ALLOW_PAGE_LOCKS=ON
    , FILLFACTOR=100
    , MAXDOP=4
);

I executed the statement by itself in SSMS, by pressing F5. It ran for over a minute, then began blocking other sessions. I then immediately cancelled the CREATE INDEX command since I cannot block other sessions.

During the first minute, nothing was blocking my CREATE INDEX command, sys.dm_exec_requests showed the process with a wait type of CXPACKET - of course. I don't think that is a bad thing since the operation was parallelized.

I didn't have a lot of time to inspect the output of sys.dm_exec_requests. There was only a single row returned from the query WHERE session_id = xxx. The blocked sessions were attempting to insert rows into the target table.

I don't know how long the locks lasted, except to say I cancelled the execution of the statement around 2 minutes after it started. Blocks were occurring for around a minute at that point.

Am I misunderstanding the implementation of WITH (ONLINE=ON)? Or is there something else I need to be aware of?

The server is a fairly beefy machine, with 2 quad-core Xeon E5-2643 3.3Ghz processors, 192GB RAM, and SAN storage capable of 5,000+ iops. CPU is typically below 20%, RAM is 93% utilized, mostly by SQL Server. There is nothing else running on the box, just Windows Server 2012, and SQL Server 2012.

Answer 1

When creating an index with online = on, the create index process will not block when creating the index object itself, but when it comes to near the end of the process, it will acquire a schema modification lock* for a period in order to actually add the index to the table, this lock type will block all outside operations until the lock is released, which could account for your blocking issues.

* An Sch-M lock is not required for online build of a new nonclustered index, though it is required in all other cases. A new nonclustered index requires only a table-level shared lock during the final phase, same as was needed during the preparation phase.

See this White Paper for details:

Online Indexing Operations in SQL Server 2005

As suggested by Mushtaq Mohammed in a comment on the question, also see:

Unicorns, rainbows, and online index operations by Paul Randal