I want to add three included columns to an existing non-clustered index. With that I'm solving performance issues in the database.
Here is some information about the index:
Rows: 112 Millions
Disk space: 30 GB
I'm using SQL Server 2008 R2 Enterprise Edition.
I'm running CREATE INDEX … WITH DROP EXISTING = ON, ONLINE = ON
In my QA environment I'm currently running the CREATE INDEX statement. It has been running 1 1/2 hours from now and I run on purpose an INSERT statement in another SSMS window, this statement is blocked since the CREATE INDEX operation is running.
In production this database is being accessed 24x7x365, so I can not accept so much time of blocking, but there's a time when the database has low access (3AM – 6AM).
I was expecting that the WITH ONLINE = ON behaves different. There's a way to prevent so much blocking? Or should I ask for a long maintenance window for the CREATE INDEX operation?
Thanks in advance for any response.