how often should indexes be rebuilt?

In oracle you rarely need to rebuild indexes no matter whether you do
massive inserts or deletes. Does the same hold true in sql server?"Ryan" <rgaffuri@.cox.net> wrote in message news:<4OCPb.5817$_H5.4427@.lakeread06>...
> In oracle you rarely need to rebuild indexes no matter whether you do
> massive inserts or deletes. Does the same hold true in sql server?

It shouldn't really be necessary to rebuild indexes, unless there's
some specific issue, such as index corruption, or you're moving tables
and indexes around on filegroups. Sometimes it can be useful to drop
indexes before a large data load, then create them again afterwards.

What is more useful is to UPDATE STATISTICS on tables manually after a
large data operation. Although MSSQL manages statistics automatically,
when you have a very large table it's possible that an update will
affect enough rows to require a new query plan, but not enough to
trigger the automatic statistics update. So it's common to do that
after a large load.

Simon|||Ryan (rgaffuri@.cox.net) writes:
> In oracle you rarely need to rebuild indexes no matter whether you do
> massive inserts or deletes. Does the same hold true in sql server?

I would like to voice a different opinion from Simon. Rebuilding indexes
can have considerable improvement on performance. For instance, some weeks
ago, I was analysed some performance problems at a customer site. One of
the actions I took was running DBCC DBREINDEX on the involved tables.
For the particular test case I had, this reduced execution time with 25%.

While the real solution to the problem was something else - and which I
only found by chance, when I had given up to achieve better than I had -
I think still illustrates that reindexing can have significant effect
on performance.

So if you have a high rate of insertions and deletions into some tables,
reindexing at least weekly can prevent your system from deteroirating.

But there may be exceptions. In our system, there is a table which I've
told our admin not to reindex, because the way the table is updated, the
rate of defragmentation is small, and reindexing usually have the effect
that the nightly load into this table runs for five minutes rather than
30-40 seconds.

You can examine the fragmentation for a table with DBCC SHOWCONTIG.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp