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.

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
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... Commented May 20, 2015 at 6:20

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.