Home > dba, observation, sql > Locking While Dropping or Altering an Index

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 an index, 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.

*Update
[qtweet 68491945105821696]

Categories: dba, observation, sql Tags:
  1. Tony Lucero
    January 28th, 2013 at 12:21 | #1

    “hypothetical index since neither are used by active queries?”

    I wouldn’t be quite sure about that. I seen hypothetical indexes referenced in execution plans. I believe that internally the optimizer must divert back to the PK but have no evidence to backup. However whats worse is proper stats arn’t kept on the tables and furthermore the optimizer can’t offer proper index choices to help you index correctly. This is junk the last “DBA” left me from a failed index tuning job across of course all the busiest environments in my data center. I came here in the situation your in and like you a downtime is in the near future….

  2. kb
    October 20th, 2014 at 16:05 | #2

    If I have a script that checks through many tables and does the Reindex only if the table meets certain criteria, would all the tables in that criteria be locked or just the current/active table that is being be re-indexed

  1. No trackbacks yet.

%d bloggers like this: