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.