Statistics - ' Auto Update Statistics ' - 'Auto Update Statistics Asynchronously'

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Statistics - ' Auto Update Statistics ' -...

Statistics - ' Auto Update Statistics ' - 'Auto Update Statistics Asynchronously'

Rate Topic

Display Mode

Topic Options

Author

Message

curious_sqldba

Posted Monday, February 25, 2013 11:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:11 PM
Points: 1,172, Visits: 2,686

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

Lowell

Posted Monday, February 25, 2013 12:15 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 11,627, Visits: 27,692

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

Sean Pearce

Posted Monday, March 04, 2013 2:50 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 345, Visits: 1,323

Lesser of two evils really.

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

http://thesqlguy.blogspot.com/

Post #1426143

« Prev Topic | Next Topic »

Permissions