3

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?

Improve this question
0

1 Answer 1

Reset to default
1

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

Improve this answer
7
  • Thanks for the reply, I read that, but as we can see from the screenshot, its an exclusive lock and also the locking happened at the beginning of the index creation.
    – DMDM
    Apr 26, 2017 at 7:06
  • In an online rebuild, the X locks you are seeing are on the new index structure being built, not the current index structure
    – George K
    Apr 26, 2017 at 7:11
  • ok, but then it wouldn't explain the blocking which I am seeing at the starting the index creation.
    – DMDM
    Apr 26, 2017 at 9:00
  • there is also a lock at the beginning of rebuild. So if you hace any data modification activity it is likely to see blicking at this stage
    – George K
    Apr 26, 2017 at 10:57
  • @SmokingDBA - Note that, since this is the creation of a new index, there's no existing index; any chance that would result in different locking activity than rebuild. I know the rebuild is functionally creating a new index, but could this be part of the issue?
    – RDFozz
    Apr 26, 2017 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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