4

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.

4

You need to analyze the wait chain. What is likely happening is that the OIB has completed the first two phases (perhaps long ago), and is now waiting for user queries to drain in order to do the final phase. One or more user queries is blocking OIB final phase, and as OIB has requested an SCH_M lock it places every other user queries in the wait list. The culprit is the user query blocking the final phase. Look at sys.dm_exec_request.blocking_session_id but make sure to walk up the chain until you find the one spid that is blocking w/o being blocked in turn.

1
  • There's no session that is blocking the Create Index operation, I have checked myself with spWhoIsActive. I decided to run the statement in production, and it have blocked the whole table for about 15 minutes and I had to stop the operation. Any ideas why is this happening? Also I executed the statement again in the QA environment, the second time did not caused blocking. It seems very weird the behaviour... – Luis de Haro May 20 '15 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.