Beware of SQL Server Online Index Rebuilds with an unexpected Update Stats causing locking and deadlock storms on complex distributed environments

Hello,

Hope everything is running great! It is not that uncommon, that large/complex database environments might have an overlapp of maintenance commands being executed, being one of the classics the one bellow:

1. Tipically DBA’s design their maintenance processes (that might include Integrity Checks/ Index Rebuilds/Reorgs/ Update Stats) to run on a daily basis under a low activity “window”.
2. Although it is a low activity window, it doesn’t mean that there is no activity and we migh have other processes that run that not only load data but also runs an update statistics after load is complete with the objective to have the most accurate statistics for the query optimizer…

…An environment can be configured like this for months and there is a never an issue… Until there is a day that while the automated daily maintenance is rebuilding an index online(1) and another process(2) after loading data runs an update statistics.

1st, If the above happens, the update statistics is blocked, because it requires a metadata lock that is incompatible with the lock placed by RebuildIndex(even with Online ​=True), this part I can accept (although I believe that there could be an alternate algorithm to make this work in a slightly different way) in the end, when the Rebuild Index finishes, the update stats will likely be a dealock victim:

Where I believe there is space for improvement is to avoid that other T-SQL that tries to run and needs to be recompiled (that will need to access stats), is blocked because of the update stats that is blocked by the index rebuild… I believe that there is space for improvement here and avoid the deadlock storms like the example bellow (this is not deadlock graph art, it is just a deadlock graph from SystemHealth XE involving several requests):

Key takeaway: If there are processes that have a step to update stats please communicate to avoid wasting computing resources and/or similar situations like the one above.

All the best and have a nice week!

Paulo Condeça