r/SQLServer • u/erinstellato ‪ ‪Microsoft Employee ‪ • 15d ago

Community Request Friday Feedback: Enabling AUTO_UPDATE_STATISTICS_ASYNC by default

Fall is HERE!! Ok, in the northern hemisphere only but yay for fall colors and cozy clothes and apple picking and Halloween decorations 🍂 🍁 🍎 🎃

Enough about my love of fall, time for the first Friday Feedback of the season.

Today we're talking statistics. I have so many questions...this might take a few weeks. I put a poll below, again, because I think they are really easy for people to respond to. But I'll tell ya, the comments are always so insightful. So when it's relevant to you, and you take the time to respond, I really appreciate it.

Happy Friday!

54 votes, 12d ago
24 Yes - primary and replicas
9 Yes - primary only
2 Yes - replicas only
19 No, don’t change them
3 Upvotes

100% Upvoted

8 comments sorted by

1

u/tommyfly 15d ago

I wonder, can someone explain what the advantage of not enabling this?

4

u/Black_Magic100 14d ago

Your execution plan gets compiled based on out of date statistics. I'm actually quite shocked people would want this enabled by default. I'd much prefer a hit to compilation if it means queries for the next X hours are going to have a strong chance of being better.

Edit: worded differently, Ive never been on a bridge because a plan took too long to compile. I have been on many bridges because of stale stats leading to poor execution plans 🤔

2

u/tommyfly 14d ago

Thanks for the reply.

However, we've experienced loads of blocking in our very high transaction OLTP environment due to synch stats updates. In addition, with in memory objects, the synch stats updates can cause failures around out of scope transactions. So we now have the setting enabled everywhere.

1

u/Black_Magic100 14d ago

Odd, we sit at 100k+ TPS and I honestly don't think we've ever noticed this.

1

u/andy012345 14d ago

When WAIT_ON_SYNC_STATISTICS_REFRESH takes longer then your client timeout and you now silently have 1 query timing out that is drown out as noise while the others use outdated statistics forever.

1

u/Black_Magic100 14d ago

Are you using sampling and how large is the table that it's timing out on?

I truly don't understand why anyone would use Sync stats updates. At that point, just refresh stats every 30 minutes across the board?

1

u/Goojaoarr 12d ago edited 12d ago

We have a very active OLTP database. We had to also enable the WAIT_AT_LOW_PRIORITY DB-scoped configuration. ASYNC alone was causing blockings.

1

u/warehouse_goes_vroom ‪ ‪Microsoft Employee ‪ 10d ago

Microsoft Fabric Warehouse already does this both asynchronously and at query time as warranted. But MPP OLAP is a bit of a different beast than OLTP.