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.