SQL Server 2012-Rebuilding online clustered index locks the table

Question

SQL Server 2012-Rebuilding online clustered index locks the table

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
June 3, 2014 at 6:54 am

#289502

Hi
I was under impression that rebuilding index online largely means that the index will remain available for use during rebuild and my procs and query will be able to use it during rebuild. Also my understanding was that table will be locked very briefly while the schema change will be completing.
But when I was rebuilding the clustered index online on a large table with some 3 million records, the table got locked and I was not able even to read the data from it for some 5 minutes. Then I cancelled the operation as it was production server and it was one of our main transaction table.
Is rebuilding index online supposed to work this way? The table has no other index.
The parameteres I used are:
REBUILD WITH (PAD_INDEX = ON, SORT_IN_TEMPDB = ON, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95)
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

In my experience, the most common cause for what you are seeing is that you are not on Enterprise Edition of SQL Server.

If you are, see Books Online for this topic:

How Online Index Operations Work

Also, what were the exact blocking locks you saw (I highly recommend sp_whoisactive for checking this)?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 2

thanks for reply. I am on enterprise:

Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64)

Oct 25 2013 19:04:40

Copyright (c) Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

I'll see the links you shared.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

jasona.work SSC Guru Points: 50084 More actions · Answer 3

It's not entirely true that an online index rebuild takes *no* locks. It takes (generally) very short locks at the beginning and the end of the process.

Here's an article that explains why you may see locking when doing an online rebuild:

SQL Skills: Misconceptions around index rebuilds[/url]

Check out Myth #5

JeremyE SSCoach Points: 16125 More actions · Answer 4

I've seen this happen when processes spawn multiple SPIDs in an ERP system. It creates a situation where there is no deadlock but also no way to resolve the conflict without killing the online index rebuild.

For example:

SPID 1 accesses Table1 (shared lock).

SPID 2 starts an online index rebuild of Table1 (shared lock).

SPID 3 accesses Table1 (shared lock).

SPIDs 1 & 3 were created by the same application process and SPID 1 is waiting for SPID 3 to complete.

SPID 1 is waiting with a shared lock for SPID 3 to complete.

SPID 3 cannot complete because SPID 2 is ahead and needs a schema modification lock to swap in the new index.

SPID 2 cannot complete because SPID 1 still has a shared lock and won't release it until SPID 3 completes.

This creates a kind of "infinite loop" which requires the online index rebuild to be killed. I worked around this by adding SET LOCK_TIMEOUT to my index rebuild script.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 5

Hi Jeremy

This one was interesting to know and could very well be the reason for locking that happened on my table for 4-5 minutes and ultimately I had to kill the rebuild command. I'll use the option you specified.

thanks

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.