Performance tuning tips from the SQLGrease team

Does rebuilding an index cause blocking?

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.

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:

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.