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.