MS SQL Server - Will auto updates overwrite a full scan

Author

Will auto updates overwrite a full scan

Massa Batheli

2006-10-24, 6:33 pm

Anyone tested this?

Does auto update stats when it kicks in overwrite a full scan?
A full scan is run every night and update updates/creates are turned
on.

Puzzle
A table has 200000 rows and with auto updates the sample rowcount is
about 50000.
If auto updates kick in will the new sample be about 70000 or it will
keep the better stats?

Will try to tailor a test for this but if anyone has a
response/tested this that will be greatly appreciated

John Bell

2006-10-24, 6:33 pm

Hi

Statistics will be updated when the query optimizer has determined that they
are out of date. You can check to see if they have changed by checking if the
statversion column in sysindexes has changed.

You may want to read about how statistics are update in "Inside SQL Server
2000"
by Kalen Delany ISBN 0-7356-0998-5

Also check out
http://msdn.microsoft.com/library/d...l/statquery.asp

John

"Massa Batheli" wrote:

> Anyone tested this?
>
> Does auto update stats when it kicks in overwrite a full scan?
> A full scan is run every night and update updates/creates are turned
> on.
>
> Puzzle
> A table has 200000 rows and with auto updates the sample rowcount is
> about 50000.
> If auto updates kick in will the new sample be about 70000 or it will
> keep the better stats?
>
> Will try to tailor a test for this but if anyone has a
> response/tested this that will be greatly appreciated
>
>

Sponsored Links