At 03:25 AM 28/01/2007, you wrote:

>I have a question to piggy-back onto yours! Does SET STATISTICS work or
>should we be setting the index inactive and active again. I have read
>conflicting articles online and am not sure which one works best. We
>have to dump in large quantities of data on a daily basis (175k per
>table for 5-7 tables) and would like the stats to be up to date. After
>setting the indexes inactive and active again by hand between each of
>the large table steps (the tables are linked using foreign keys) we have
>noticed a 15% decrease in time. But I am not sure of the difference
>between doing that and running set statistics. Anyone know if one way
>is better then another?

One is not "better" than the other. They do different things. SET
STATISTICS calculates the selectivity, that is, how close the values
in the index are to uniqueness (the higher the number, the lower the
selectivity). The optimizer uses this information to choose indexes
for the plan.

Rebuilding an index is a physical operation. It causes the index to
be freshly laid onto disk. This means that there will be no leftover
debris from deleted data, so the number of levels of indirection may
be reduced, there may be fewer index pages to be read, and there may
be more contiguity between the different pages that are storing the index.

Every little bit helps, of course, although restoring the database
from a backup is the ultimate squeaky-clean experience because each
index's pages will be guaranteed contiguous (no pages to re-use) AND
the statistics will be fresh.

./heLen