1

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.

| improve this question | | | | |
  • I would suggest downloading and using sentryone.com/plan-explorer to get more details as to how this blocking is occurring. Also, it seems by your statements, that you have both an index reorg and a stats update running at the same time or the jobs are staggered and running into each other. Also, are you sure it is a reorg and not a rebuild? – Sean Perkins Apr 12 '17 at 16:34
  • @SeanPerkins where does plan explorer tell you about blocking? – Erik Darling Apr 12 '17 at 16:49
  • We just have the Index Reorg running (and I am sure it is a reorg). This is being done via a SQL Server Agent job with Ola's scripts. We don't have – john milner Apr 12 '17 at 18:29
  • a stats update scheduled at the same time. I believe what is happening is that SQL Server is updating the statistics because we have Auto Update Statistics enabled. I have been logging sp_whoisactive to a table every 30 seconds to follow the blocking chain. The session that I believe is SQL Server update statistics has no query text or query plan. But as soon as I stop the reorg this session goes away and I see statistics on the table being reorged are updated at the same time. – john milner Apr 12 '17 at 18:37
2

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

| improve this answer | | | | |
  • I will try #1 and #2. We are currently at 10% for reorg and 30% for rebuild. After doing this would you recommend turning Auto Update Statistics back on? – john milner Apr 12 '17 at 18:42
  • @johnmilner updated answer – Erik Darling Apr 12 '17 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.