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.