Index Reorgs Blocking Statistics Updates Blocking Inserts

Question

After upgrading from SQL Server 2014 to SQL Server 2016 last month, we are seeing statistics updates on a table being blocked for hours while a table’s indexes are being reorganized. Inserts are in turn being blocked by the statistics update.

The database in question initially had Auto Update Statistics enabled and Auto Update Statistics Asynchronously set to disabled. We tried enabling Auto Update Statistics Asynchronously but saw the same problem. Last Friday we disabled both Auto Update Statistics and Auto Update Statistics Asynchronously and have not experienced the blocking chain again.

This has been extremely problematic since we get a large bolus of data in each night and this blocking chain prevents that data from filing prior to the start of business hours. I am discussing running index reorganizing less frequently with the rest of the team but am getting some resistance.

Are reorgs blocking statistics updates blocking inserts something you have seen before? What are we losing by disabling Auto Update Statistics and Auto Update Statistics Asynchronously? We are already using Ola’s scripts to update all statistics on a weekly basis.

Answer 1

Here's what I'd try:

1. Reschedule my reorgs to occur outside of data loads
2. Change the threshold that I reorg to a much higher value than the default for Ola's scripts (5%) to something like 50%. Also crank rebuilds up to 70 or 80% if you're using them.
3. Stop running reorg and rebuilds all together, and just update stats after a data load

Update: For auto update stats, you could

1. Turn them off during data loads, and back on afterwards/after stats updates run from your job
2. Leave them off and run stats updates jobs at regular intervals during the day

Hope this helps