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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
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
    
There are times where we have less activity; I was trying to avoid coming in at 4am. :-) – Max Vernon Apr 29 '13 at 21:02
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
    
Why do you have to "go in"? Schedule a job and get a good night's sleep - waking only if you need to (e.g. something goes wrong). – Aaron Bertrand Apr 29 '13 at 23:49
    
I'm really just not willing to have to rely on someone or something telling me stuff is no longer working. "People" say our site(s) going offline for 5 minutes is causing our Google Rank to drop. Clearly I'm not convinced of that myself, but hey if that's what the "man" wants... – Max Vernon Apr 30 '13 at 2:13
up vote 7 down vote accepted

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

share|improve this answer

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.