August 3, 2007 at 2:19 pm
Do SQL Server 2005 statistics become more sensitive as a table gets larger? For example as a table gets larger do statistics get out of date for a smaller number of changes to data in the table. For tables in my production database this seems to be the case. One table has 75 million rows and statistics get out of date after just 5000 rows of data changes.
August 6, 2007 at 9:23 am
If you actually think about what you're asking and what you've posted you'll sort of realise you've both asked a silly question and answered your question < grin >
There is no magic anything when sql server designates stats out of date - this state does not exist, stats are not marked out of date - so no sensitivity.
However, your queries may or may not be affected by a %age of changes to a column in a table. When we talk about out of date stats it is "us" the DBA's who use this phrase to mean that the statistics held on a column are no longer suitably relevent to the query.
Now .. if you're referring as to when auto update stats events occur - then this is well documented by microsoft - and the larger the table the more changes have to occur before the auto stats updates occur as they work on a number of changes or %age based upon size, so for large table this quite often never happens for some time - so most people schedule an update stats. I have to say I've always updated stats on a regular basis and I've never found "out of date" stats actually affecting a query. Changes in stats or the distribution, yes, but I've never run a query, found it not running very well, updated stats, and wow it now runs perfect - and that's in 14 years.
so if you mean auto stats then the larger the table the less this tends to happen, for small tables this may never happen too.
Hope this helps.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 7, 2007 at 6:52 am
We have run into a similar issue.. we ingest a bunch of files daily from 8M rows to 50K rows and on our 2000 boxes they all ingest relatively smoothly and quickly, however when working on SQL2005(64) after the 3rd file ingestion the times jump up dramatically after each successive file.. if we run update stats on the DB in question they fly again. Any ideas anyone?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply