What is the recommended frequency for the rebuilding of
the indices of a SQL 2000 production DB to ensure
responsiveness of the DB?

It depends on user requirements on frequency of querying
the data,how much data is being loaded, frequency of data
load.

1. If the data is loaded everyday in the warehouse and the
users query the data from the next day itself, then its
recommended to rebuild the indexes everyday after load.

2. In some environments, data is loaded into the DWH
databases everyday but the users will be querying against
the database only till the previous week. So building the
indexes once a week is good at this scenario.

3. If the tables are too huge and everyday/week if
millions of records are loaded into the tables, updating
the statitical page is recommended (Please check
sp_updatestats from BOL )instead of rebuilding the indexes
since this takes lot of time and resources.

FYI: 1. Rebuilding the indexes is required only after
the "Logged and Minimally Logged Bulk Copy Operations" of
data load.
2.If a large amount of data in an indexed column has
been added, changed, or removed (that is, if the
distribution of key values has changed), or the table has
been truncated using the TRUNCATE TABLE statement and then
repopulated, use UPDATE STATISTICS. To see when the
statistics were last updated, use the STATS_DATE function.

Hope this information helps.

-Varad



>-----Original Message-----
>What is the recommended frequency for the rebuilding of
>the indices of a SQL 2000 production DB to ensure
>responsiveness of the DB?
>.
>

in general indexes do not need to be re-built in SQL Server 2K. You will
want to de-frag indexes after a lot of (non-squencial) inserts and deletes.
The de-frag can be done while the data table remains on line.
hope this helps.

dlr

"Jean-Marc Filiatrault" <anonymous@discussions.microsoft.com> wrote in
message news:01c201c39747$06f975f0$a601280a@phx.gbl...
> What is the recommended frequency for the rebuilding of
> the indices of a SQL 2000 production DB to ensure
> responsiveness of the DB?