Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Statistics - ' Auto Update Statistics ' - 'Auto Update Statistics Asynchronously' Expand / Collapse
Posted Monday, February 25, 2013 11:20 AM


Group: General Forum Members
Last Login: Friday, November 18, 2016 9:50 AM
Points: 1,525, Visits: 3,573

I have option ' Auto Update Statistics ' set to true and option ''Auto Update Statistics Asynchronously' set to False . I think ''Auto Update Statistics Asynchronously'' should be turned ON because all my queries would be waiting until stats are updated. Is that right?
Post #1423716
Posted Monday, February 25, 2013 12:15 PM



Group: General Forum Members
Last Login: Today @ 4:27 PM
Points: 14,543, Visits: 38,399
you might get a benefit out of it, but it's one of those one half of one percent kind of things, and not a major performance enhancements.

remember what has to happen: auto update statistics is true, and for a specific table, when that table has row changes equal to 20% of the total # of rows plus 500 more rows, auto update of stats will occur.

on that one event, for that one table, for the slice of time it takes for statistics to update, is the only time you might see a performance enhancements, because other queries hitting that happen to hit the same table would use the previous statistics, and not wait for the process to complete.

if you have a hard hitting high volume 24-7 system, you might see a benefit once in a while, but on the other hand, you'd also probably be updating statistics a lot more often as a schedule job or something, probably with fullscan, so you can be sure of optimal performance for any stats, so the setting would never have an impact.

for me, auto updates is one of those built in safeguards that help systems that don't have a DBA watching over it.


help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1423740
Posted Monday, March 4, 2013 2:50 AM

Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 3, 2016 5:19 AM
Points: 1,144, Visits: 3,432
Lesser of two evils really.

Auto Update Asynch OFF - Queries wait for stats to be built
Auto Update Asynch ON - Queries use stale stats

The SQL Guy @ blogspot


About Me
Post #1426143
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse