February 5, 2010 at 12:57 pm
statistics are only updated 'automatically' when >20% of the values for that statistic are modified. that can take a LONG time for some objects, and the staleness of the stats can lead to REALLY bad performance due to suboptimal query plans.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 5, 2010 at 2:00 pm
AGREE
with the SQLGuru.
Auto update stats is not adequate.
There's a whole complicated selection algorithm for stats updating based on the rowcount and rowmodctr from sysindexes.
http://support.microsoft.com/kb/195565
It doesn't catch everything, that's for sure.
Craig Outcalt
February 5, 2010 at 2:16 pm
For most cases, auto update stats or sp_updatestats is good enough. For those instances where you need more, you should schedule that separately and with the appropriate sampling rate.
I have heard of situations where people have scheduled an update stats with full scan several times a day on specific tables. But, that is generally an extreme situation where they have specifically identified that the default sampling rate and/or the frequency of auto update stats was not adequate.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 5, 2010 at 5:27 pm
thanks for your hep..
But is there no way to find exactly that these Statistics are up to date and these Statistics are outdated in a particular database?
February 5, 2010 at 5:30 pm
You have the function STATS_DATE - you can use that to identify when the stats were last updated. You can review the stored procedure sp_updatestats to see how this procedure identifies stats that need to be updated.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 5, 2010 at 7:28 pm
Mani-584606 (2/5/2010)
thanks for your hep..But is there no way to find exactly that these Statistics are up to date and these Statistics are outdated in a particular database?
Yep.
see my previous post.
That's the algorithm that MS uses for the auto update stats. That's when MS says stats are out of date.
It's a pretty good rule of thumb and good for most things.
I dial them back a little and try to update stats overnight when people aren't using the system...
so for example I do them at 15% instead of 20% on large tables. Tweak to your heart's desire, but I think it's fair to say that you've let it go too long in some cases if you rely solely on auto update stats.
A combination of STATS_DATE and the MS algorithm might catch more exceptions, too.
good luck
Craig Outcalt
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply