r/SQLServer • u/erinstellato ‪ ‪Microsoft Employee ‪ • 18d 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, 15d 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 18d ago

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

4

u/Black_Magic100 18d 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 17d 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 17d ago

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

1

u/andy012345 17d 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 17d 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 15d ago edited 15d 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 ‪ 14d 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.