Prev: Ceckpoints in SQL 2005
Next: Database-Level Roles documentation in BOL
From: Bero on 11 Aug 2010 16:11
-In SQL2005, it is OK to have older stats provided the table has not
changed. How old is really older for optimizer or it doesn't care as
long as table table has not changed?
-Auto updates stats will update table stats only if the table data has
changed more than 20%.
-If the database auto stats update is on. It will update non-indexed
stats as SQL Server feels appropriate. I do not need to worry?

If my above assessment is not accurate, please let me know what the
best way to update stats if I do not want to bring all my activities
down. Any recommendations..
From: Erland Sommarskog on 11 Aug 2010 16:57
Bero (robertatnova(a)gmail.com) writes:
> -In SQL2005, it is OK to have older stats provided the table has not
> changed. How old is really older for optimizer or it doesn't care as
> long as table table has not changed?

It is not that the statistics whither by itself. That is, the only thing
that could render the statistics bad is changes to the data.

> -Auto updates stats will update table stats only if the table data has
> changed more than 20%.
> -If the database auto stats update is on. It will update non-indexed
> stats as SQL Server feels appropriate. I do not need to worry?

That depends. There are situations where auto-stats work very well.
But there are also scenarios there auto-stats is insufficent. One
very common situation is that you have a big table with index column
that is monotonically growing and where you typical run queries about
the most recently inserted rows. For instance, orders inserted the
last week. Since auto-stats sets in only when 20% of the rows have
been updated, the statistics will most of the time say the for the
intervall of the last week, there is at most one row. When the real
number might be 100000 or more.

Now remember, that if you reindex a table, statistics are updated as a
side process. So if you have a maintenance plan which defragements
everything blindly, you have little reason to worry about statistics.
(Unless the effect I describe above sets in already after a day or
two, and you reindex once a week.) If you do "smart" defragmenting,
and only reindex when needed, you are likely to have problem with
these ascening columns, since indexes with monotonically increasing
values don't get fragmented.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Ceckpoints in SQL 2005
Next: Database-Level Roles documentation in BOL