Auto updatestats ignoring previous increase in sampling
-
I have found that for a couple of different systems I have worked on, when the tables are relatively large (say 1 million+ row) the default sampling rate on the statistics is not enough to ensure the optimizer makes a good decision. Usually, running dbcc updatestats for these tables with either fullscan or a sample of 50% or higher will fix the problem.
My issue is that if the auto updatestats fires off before the next weekly manual update stats job, the auto updatestats lowers the sampling rate back down to the default, thus putting the optimizer back into a position where it stops using some important indexes.
My feeling is that in this case it would be better to disable the auto updatestats on just these few tables.
Any comments or ideas regarding this chain of thought? Anyone see a similar problem and come up with a good solution? I have not found where I can alter the sampling rate permanently, am I just missing something?
MCurnutt
-
I usually update stats every night. I generally turn off auto-update stats preferring to manually update them, my thinking is that if stats do need updating automatically there's probably a good chance I won't actually want them to do this when they decide to update ( probably a busy time ) However, I guess it's probably a personal choice. The last technet meeting I attended did suggest that having the auto-update on in a busy database might not be the best choice.
You can of course set the auto-stats OFF for individual tables/columns which may solve your problem.
I believe that the auto stats uses default sampling ignoring any previous settings.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
We manually update the stats each evening too, letting sql decide when to do it tended to cause a few too many issues (it always kicked off at the busiest point of the day).
Colin is right that regardless of the sample rate you manually update the stats with the auto update will default back to 10% when it rund (another reason not to let it run in my book)
-
Some links
http://support.microsoft.com/default.aspx?scid=kb;EN-US;195565
http://www.microsoft.com/sql/techinfo/tips/development/queryopstats.asp
http://support.microsoft.com/default.aspx?scid=kb;EN-US;325024
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply