Disable and Rebuild or Drop and recreate Index

[dsdevonsomer@gmail.com]

Hello all,
I am new to managing indexes on large tables and need some help.
Hopefully, I am not repeating question here. I searched as much as I
can, but not finding relatively best answer..

Here is my scenario.
I have 2 tables with more than 4 mil rows (1 - 2.8 Mil, 2 2.1 mil). Of
these tables, there are about 25 ( select ) queries run to help
generate reports every week.

Also, every week, there will be some new data in both tables (about
25k in each tables). To improve select performance, based on few
columns that are frequently used in those queries, I added Non-
Clustered Indexes on these columns ( about 4 in each table ) on both
tables.

Now, for the first time, the performance was great.. And I am now on
2nd week, where I have to import new weekly data. I am debating as to
disable and rebuild all index or just drop and recreate.

I have tried drop & recreate, it takes about 1.5 hr to finish, which
then defeats the performance improvement argument.

Can someone please share their expert knowledge/experience about the
best way to use indexes in this scenario?

Many thanks,
JB

[Erland Sommarskog]

Re: Disable and Rebuild or Drop and recreate Index

(dsdevonsomer@g mail.com) writes:

Here is my scenario.
I have 2 tables with more than 4 mil rows (1 - 2.8 Mil, 2 2.1 mil). Of
these tables, there are about 25 ( select ) queries run to help
generate reports every week.
>
Also, every week, there will be some new data in both tables (about
25k in each tables). To improve select performance, based on few
columns that are frequently used in those queries, I added Non-
Clustered Indexes on these columns ( about 4 in each table ) on both
tables.
>
Now, for the first time, the performance was great.. And I am now on
2nd week, where I have to import new weekly data. I am debating as to
disable and rebuild all index or just drop and recreate.
>
I have tried drop & recreate, it takes about 1.5 hr to finish, which
then defeats the performance improvement argument.

Did you try importing the new rows with the index present?

I would tend think that if you only import 25000 rows, that the
penalty you will get from having the indexes in place will not be
that severe.

Then again, I would not really expect it to take 1½ hour to drop and
rebuild four non-clustered indexes on a four-million rows table.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx