Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Locking While Dropping or Altering an Index

Yesterday I was trying to drop some hypothetical indexes in production against a fairly active table. I started causing blocking so I had the bright idea of disabling the indexes first, then dropping. Well, that didn’t help, even when setting DEADLOCK_PRIORITY to LOW. I ended up waiting until early morning to remove those indexes to prevent from blocking other users.

Finding no info on the web about the locks taken during the process of dropping or disabling an index, I set about doing this small bit of research.

The queries I used:
In Window 1 (Execute first)

BEGIN TRAN
SELECT TOP 100 * FROM myTable WITH (TABLOCKX)

* I use TABLOCKX to simulate many updates going to this table.

In Window 2

DROP INDEX [ix_myTable_testIndex] ON myTable

Using Adam Machanic’s (blog | twitter) sp_WhoIsActive I was able to get the lock details easily.

sp_whoisactive @get_locks = 1

When you disable a lock, you end up trying to acquire a Sch-M or Schema Modification lock.

<Database name="Sandbox">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="myTable" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

Then the next select query comes along and trys to grab an IS or Intent Shared lock

<Database name="Sandbox">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="myTable" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IS" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

So at this point yesterday, I was thinking, maybe I can disable the index and then drop it. Well, ALTER INDEX … DISABLE also waits on a Sch-M lock before it can proceed. I’m not any better off. Even after the disabling the index, SQL Server still tries to grab a Sch-M lock to drop the disabled index. All these rules also seem to apply to hypothetical indexes. I’m sure there’s some internals reason why it does this, but why worry about locks when dropping a disabled or hypothetical index since neither are used by active queries?

Lesson learned, drop indexes when the table usage is low.

Comments

Posted by Steve Jones on 12 May 2011

Good advice, and worth knowing if you have a busy system.

How long does it take to drop the index? Is it that big a deal to wait for a  Sch-M lock? Or does that cause you insert/update issues?

Posted by Eric Humphrey on 13 May 2011

Steve, it depends on the activity of the table. Usually the dropping itself pretty quick, it's the blocking that causes that waiting. And then other incoming queries get blocked behind that. That's the kicker. I wouldn't care how long my drop indexed was blocked, as long as it wasn't blocking other queries behind it.

Posted by Winston Daley on 17 May 2011

Working like this in the trenches is always "priceless".

However, I am just wondering if it would have made any difference if Eric had first used the sp_Indexoption to set the preferred lock, on the table, rather than let the system choose the lock type, also, what was the size of the TempDB database, to facilitate this index operation, was the index on its own Filegroup and was enough space available for this action?

Also, would Checkpointing the table help in any way, on a heavily used table in production?

Posted by Winston Daley on 17 May 2011

In addition: The DROP INDEX [ix_myTable_testIndex] ON myTable, indicates a SQL Server 2000 operation, wouldn't a Create Index [ix_myTable_testIndex] ..., With Drop Existing,  be a better choice, providing there is enough available space in TempDB for this operation?

That's my 2cents, your thoughts?

Posted by Eric Humphrey on 21 May 2011

Winston, this was purely for DROPPING an index. Also, take a look at Paul Randal's comment at the original location: www.erichumphrey.com/.../locking_drop_index

Leave a Comment

Please register or log in to leave a comment.