Cory--

We do something similar to maintain a table with dated events. This table has about seven million records and it is imperative to drop the indexing prior to rebuilding the table because of the slowdown the indexes cause. In our system the total job takes about 90 minutes by dropping and reapplying indexes and about six hours if the indexes are left in place. Depending on the number of records involved, YMMV.

--Jim Howell

-----Original Message-----
From: Ellingson Cory (RCIS)
[mailto:mssqldba-ezmlmshield-x5019876.[Email address protected]
Sent: Thursday, June 30, 2005 1:47 PM
To: LazyDBA Discussion
Subject: Index question...


Hello all!

I recently was informed of a database I now get to support. I have Lumigent
Log Explorer, and I have it set up to tell me when certain commands happed
(new tables, dropped tables, dbcc's, new indexes, etc) and I get an email
daily at the same time about an index that is dropped and recreated. After
some looking, I found a SP that issues this command, and it is part of a DTS
package. It appears the reason for this is there is a SP that appears to
look for duplicate records to remove before the final step to refresh a DB
user table. This index is a clustered index; the table is rebuilt each
night from numerous other tables...

Now, the question...

Is it necessary to drop and recreate this clustered index over a table that
is built fresh each night, or will the index continue to resort as it is
written?

I want to get rid of this statement...I was thinking my next step would be
to do a DBCC DBREINDEX (<table>, '<index name>'). I first wanted to see if
I needed it at all.

TIA

Cory Ellingson


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page