Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)),
);
GO

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
);

This 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.

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.

share|improve this question
    
How long did the locks last? The msdn page hints that you may have some short-term (I'm assuming page- or row-level) locks during the build process. –  JNK Apr 29 '13 at 19:52
    
What were the sessions being blocked doing? (Also if the process said CXPACKET there must have been multiple rows, and one of them must have been something other than CXPACKET...) –  Aaron Bertrand Apr 29 '13 at 19:56
    
Are you using a BEGIN TRANSACTION...COMMIT block on the command? According to this link that would cause a lock to be held until the end: msdn.microsoft.com/en-us/library/ms190981.aspx. –  Shawn Melton Apr 29 '13 at 20:01
2  
I think a couple of minutes to initialize a snapshot of 150 million rows is pretty reasonable. Everybody is "24x7x365" but surely there are less busy times that may be less problematic for this interruption. You also may consider splitting up the work using one or two filtered indexes if UserViewedDetails is always used as a filter. –  Aaron Bertrand Apr 29 '13 at 20:53
2  
I don't think there's much chance of diagnosing this without DMV dumps of the blocked sessions. FWIW I've carried out similar index builds on 20 million row tables under constant use, without seeing any blocking. –  Mark Storey-Smith Apr 29 '13 at 21:12
show 9 more comments

1 Answer

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.

share|improve this answer
    
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. –  Paul White Mar 26 at 20:08
    
Cheers for the white paper, Paul. –  Max Vernon Mar 26 at 22:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.