Statistics - ' Auto Update Statistics ' - 'Auto Update Statistics Asynchronously'
-
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?
-
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!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply