Sql-server – Online index operation blocking inserts

nonclustered-indexsql serversql server 2014

I am trying to create a non clustered index on a table which is around 500GB. The table already has a clustered index on it. Since we do not want other transaction to be blocked we tried using ONLINE=ON, however at the beginning of the index build it blocked other OLTP transactions.

When I checked what locks were being held I noticed exclusive table lock on the table and other transactions are getting blocked by this.

Screen shot:

enter image description here

I look at Guidelines for Online Index Operations for exclusions and its not applicable for this table.

Any idea why this is happening?

Best Answer

Rebuilding an index online doesn't allow you to completely avoid locking - when the process completes the table is locked for a brief period and the new index replaces the old one.

Online index operations need to take two very short-term table locks. Shared)table lock at the start of the operation to force all write plans that could touch the index to recompile, and a SCH-M (Schema-Modification) table lock at the end of operation to force all read and write plans that could touch the index to recompile.

And the article you provided has some info on this as well: enter image description here

Related Question