SQL Server Performance Forum – Threads Archive
Dropping indexesHi all, is it compulsury to drop and create indexes if we copy 100 thousand rows per day (everyday)from a table on one database to another table on another database? Thanks in advance.
That’s a quite a number of rows. Well depends on the fragmantation level of the index. If the fragementation is too high, you should consider reindexing, else its not required. Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
How many rows are in the table? from what I remember, if you are inserting more than 30% rows it would help dropping and recreating indexes. Bambola.
The total no of rows are 15 million and everyday 100 thousand or less will be added approximately…thanks
It doesn’t seem to me the case to drop and recreate indexed. Bambola.
Rather you can rebuild the index using DBCC DBREINDEX for that table.
It may be hard to find window time for the optimization job for those tables which have frequent updations. HTH _________
Rebuilding the index is better than re-creating the index.
But that too is necessary only if your index is fragmented quite quickly. In your case daily 1/150 is your index fragmentation tatio.
I feel rebuilding may NOT that necessary. But any way use DBCC DBReINDEX for the table once a month or so.
G.R. Preethiviraj Kulasingham
With such huge insertions fragmentation is expected and its suggested to Rebuild the index.
And once in a week is referred than a monthly basis. _________
Claudia, BTW is it possible to archive the database/table to keep the current db speed enough? _________