Yes.  I’m always surprised when people perform index operations such as this as well as dropping and creating of indexes during the middle of the day.  These operations have an impact on performance.  In this post I’ll show you why index operations are blocking operations.  I will also discuss an option (only for Enterprise Edition and Azure Managed Instances) that allows these index operations to run with minimal blocking.

How do I see this?

If you read my post on whether or not statistics updates causes blocking you’ll see the query I’m using to display the blocking is slightly different.  I’ll explain why I had to modify it.

From my previous blog post:
SELECT tl.resource_type, tl.resource_subtype,
CASE WHEN resource_type = 'OBJECT'
THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id)
ELSE '' END AS object,
tl.resource_description,
request_mode, request_type, request_status
FROM sys.dm_tran_locks tl WHERE tl.request_session_id = 54

From this blog post:
SELECT tl.request_session_id, tl.resource_type, tl.resource_subtype,
tl.resource_associated_entity_id, resource_database_id,
request_mode, request_type, request_status
FROM sys.dm_tran_locks tl WHERE tl.request_session_id in (54, 57, 56, 61)

You may notice I don’t have the convenient decode of the table name by using the object_name() function.  An index rebuild places a schema modification (Sch-M) lock on the table.  The object_name function requires a schema stability lock on the table (Sch-S).  The schema modification lock blocks the schema stability lock.  As a result we can’t use the object_name() function on this table as the rebuild runs.  In case you hadn’t read my post on statistics update and blocking, I’ll reference this blurb from the lock compatibility matrix again:

https://technet.microsoft.com/en-us/library/ms172925(v=sql.110).aspx

In this example there are 3 sessions we’re focussed on:

Session 54: performing the rebuild
ALTER INDEX ALL ON ExecQueryWaitStats REBUILD

Session 61: executing a select on the rebuilt table
SELECT TOP 1 * FROM ExecQueryWaitStats

Session 56: the query attempting to look for the transaction locks, but getting stuck in the object_name() function call:
SELECT tl.resource_type, tl.resource_subtype,
CASE WHEN resource_type = 'OBJECT'
THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id)
ELSE '' END AS object,
tl.resource_description,
request_mode, request_type, request_status
FROM sys.dm_tran_locks tl WHERE tl.request_session_id = 54

The output from the query we are using to monitor locks is below (it is two different images since all the locks don’t clearly display on one image).

SELECT tl.request_session_id, tl.resource_type, tl.resource_subtype,
tl.resource_associated_entity_id, resource_database_id,
request_mode, request_type, request_status
FROM sys.dm_tran_locks tl WHERE tl.request_session_id in (54, 57, 56, 61)

The image below shows blocking of session 61 – which is the session that is executing the select against the index being rebuilt.

The image below shows blocking of session 56 – which is the session that is attempting to look up the locks but getting stuck on the object_name() function.

You might notice that the object_id that the SELECT statement is waiting for is different from the object_id that the object_name() function is waiting for.  The select statement is blocking on the clustered index (object id 446624634) whereas the object_name() is blocking on the table (object id 462624691).  In both these scenarios they are blocked by session 54 – the index rebuild.

Index rebuilds look nasty how do I minimize this?

The typical answer is perform them during maintenance windows where there is little load on the system.  If you are on SQL Server Enterprise Edition or Managed Instances you have some additional options.

With Enterprise Edition and Managed Instances you can perform rebuild operations with the ONLINE=ON option.  This minimizes locking as an index rebuild runs.  As with everything there are caveats.

Online rebuilds on a really busy table (lots of DML) will take longer.  I’ve seen where performing online rebuilds would not complete within a reasonable amount of time compared to offline rebuilds.

You cannot perform an online rebuild on an index that contains any LOB columns.

There is a brief period at the end of an ONLINE rebuild where blocking will occur.  It is brief; however, on a high volume system this can cause a performance impact.

Since I mentioned dropping and creating indexes in the first paragraph of this post, I think it deserves mention that these operations can also be performed with the ONLINE option.

 

Watch us diagnose a query timeout with SQLGrease:

See how you can get query level wait events without Query Store:

SQLGrease can help you quickly identify if maintenance tasks are interfering with your production workload.