0

We have a table with 10 billion records, where daily there are hundreds of thousands of updates/inserts/selects happening. As the auto-update stats was ON there were a few performance issues; hence, we turned it OFF and there were positive results.

Now, we want to turn the auto-update stats on in async mode, then perform another round of testing on it. How can the auto-update stats in async mode be enabled for one particular table?

Improve this question

2 Answers 2

Reset to default
2

You can't enable async auto-update stats for just one table. The setting is at the database level.

I would recommend keeping auto-update stats enabled and also having an UPDATE STATISTICS job that runs on a schedule, perhaps daily. You could test doing a 10% sampling on the large tables rather than a more expensive fullscan.

Improve this answer
2
  • Yes that method is already followed but if we turn on the auto-update stats with async for an entire database, it is obvious that it will be applicable to each and every tables isn't it? Also what would be its impact on the database ?
    – Karan Salvi
    Commented Nov 8, 2016 at 2:22
  • Yes it will take affect on every table. The impact is whether or not you want the query to continue if SQL thinks updating stats will make it faster, or if you want the query to continue without the updated stats but still run update stats for other queries. That's what async does: current query continues without the benefit of updated stats, but the benefit there is that it doesn't have to wait. It can be good or bad. I don't like to rely on auto update stats, either sync or async. I like to manage statistics with UPDATE STATISTICS instead.
    – Tara Kizer
    Commented Nov 8, 2016 at 2:28
0

Async update stats is recommended IMHO. Here's how it works.

  • If you don't have auto update stats, well this is obvious. You may want this to confine your stats updates and execution plan changes to set windows, and thus also control query time.
  • If you have sync update stats, if an update stats is required, and it takes too long - your query times out AND the update stats gets rolled back. This can kill applications that can't get a query through because the tables are too big for an update stats to complete in on less than 30 seconds (the default .NET SqlClient query timeout)
  • Async update stats says hey, if a query needs new stats, run it as-is now but build stats in the background for next time.

The first case is possible, the second case is extremely rare. The last case is the most common and the best default. Sure you're operating off of stale stats, but you'd be operating off of that with auto stats off anyway.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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