Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Monday, February 25, 2013 11:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, October 19, 2014 8:33 PM
Points: 1,285, Visits: 2,963

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 12,905, Visits: 32,166
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.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1423740
Posted Monday, March 4, 2013 2:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 943, Visits: 2,951
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

@SeanPearceSQL

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

Add to briefcase

Permissions Expand / Collapse