Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
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?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14944 Visits: 38940
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

--
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!

Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 3432
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search