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