Sql Server Standard 2014: Reindex job locked by other transactions On a customer of ours we have Microsoft SQL Server 2014 - 12.0.4100.1 (X64) installed. It's a heavy transactions schema (especially during nights) and we have a scheduled REINDEX maintenance job that runs every early morning at 4 am.

We have noted that tonight the reindex job got stuck for like 45minutes on a alter index on a particular table because it was locked by another transaction (an UPDATE statement issued by a user, a very common transaction for our system).

The guy responsible for the night check promprly killed the transactions (reindex included) since we were already receiving complaints from the users; when he killed it the current ALTER INDEX statement was blocked by 45 minutes, but the entire REINDEX job took at that moment almost 4 hours (he killed it at 7:51 am).

Is there a way to avoid the REINDEX job to get blocked by other transactions? 

EDIT: the fragmentation levels we noted (&lt; 30%) showed that we could probably go for a REORGANIZE instead of a REINDEX thus eliminating the chance of other transactions being locked by the reorganize, but the reorganize itself could still be blocked.
and we have a scheduled REINDEX maintenance job that runs every early morning at 4 am.


Wait..is that really necessary to run the reindex job every day ?. Do you even look at whether the indexes are fragmented ?. Since you said its a critical system I will take liberty of assuming the underlying storage is fast that further lowers the possibility of re-indexing everyday. Please think about it.


  Is there a way to avoid the REINDEX job to get blocked by other transactions?


If you absolutely want this, then run index maintenance during downtime or when load is relatively very very less and no DML operations are going on. You can use online index rebuild ( If you have enterprise edition) but that also takes lock and may be blocked. Plus frequently rebuilding index out dates stats more often leading to more compilations

Suggestion: Use Ola Hallengren Index rebuild solution.