By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,190 Members | 1,469 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,190 IT Pros & Developers. It's quick & easy.

Disable and Rebuild or Drop and recreate Index

P: n/a
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
Jun 27 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(ds**********@gmail.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 27 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.