ALTER INDEX….ABORT!

I know there are people out there that will be going from older versions of SQL to SQL Server 2016, yes it makes sense it is a great product with a lot of new features such as Query Store, Stretch DB to name  but a few.

However don’t forget about the features that were available in 2014 that are naturally available in 2016, such as the ability to control blocking behaviors when rebuilding indexes.

Classically when you want to use the ONLINE mode of rebuilding indexes the code would look like:

ALTER INDEX [MCD] ON [dbo].[mcdaol] REBUILD WITH(ONLINE = ON)

Looking into the locking you will see that ONLINE operation uses (Sch-M) on the corresponding table as part of the process (actually takes Shared Table Lock (S) at the beginning of the operation, and a Schema Modification Lock (Sch-M at the end)).

lock1

So to be granted a SCH-M lock you can’t have any conflicting locks, so what happens when / if you have a process that is updating the table and you want to use the ONLINE rebuild? Yes you will be blocked. With 2014 onwards we can control what happens if we get into this situation and for this post I am going to abort the other query causing me to wait.

Let’s see what happens.

I have 1 window running an update such as:

— Open Trans

BEGIN TRAN
UPDATE [dbo].[p_dinner]
SET og_status = 1
WHERE og_status = 10

In a different window I am running the Index rebuild:

ALTER INDEX [p_dinner_idx] ON [dbo].[p_dinner]
REBUILD WITH(ONLINE = ON)

Naturally this will be blocked.

lock2

So now we can control what happens here, for this example I want to kill the connection holding the locks that are conflicting with my rebuild i.e. the update (just an example).

So I run:

ALTER INDEX [p_dinner_idx] ON [dbo].[p_dinner] REBUILD
WITH
(
  ONLINE = ON
   (
     WAIT_AT_LOW_PRIORITY
     (
       MAX_DURATION = 1,
       ABORT_AFTER_WAIT = BLOCKERS
     )
   )
)
GO

lock4

From the above image you can see after 1 minute (MAX_DURATION) the query successfully executes because it killed the connection causing the blocking, which is done via the ABORT_AFTER_WAIT = Blockers clause.

2 thoughts on “ALTER INDEX….ABORT!

  1. Pingback: ALTER INDEX….ABORT! - SQL Server Blog - SQL Server - Toad World

  2. Pingback: Aborting Index Rebuilds – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s