October 13, 2004 at 7:26 am
I have a DTS package that runs every 5 hours bringing in data from an Oracle server. Some of the table rows that come in have between 300-500 thousand rows of data. Now to my question. Is it a good idea to re-index these tables after as part of the DTS, once the data has been brought into our production environment using the DBCC Reindex? Thanks in advance
October 13, 2004 at 9:05 am
Why would you want to Re-Index the tables? The indexes will be updated as the data is populated in the tables. If you are considering dropping the indexes so the data import will run faster and then re-create the indexes, that option makes more sense. If you are worried about fragmentation of the indexes, you could run the reindexing as a nightly job.
You should however consider this option only if there are a large number of indexes on the table and is slowing down the data import process. Also, if there is a clustered index and many composite indexes, this might affect the DTS process. However, the number of rows that you mentioned in my opinion should not require you to reindex.
If you are not fully satisfied with what I am saying, try different options. DTS with and without reindexing, with index defrag, check the performance of the process under different conditions.
It is difficult to give a perfect solution, from the information you have provided I do not know any other loads on the server. If data is being queried on, as you run the DTS. How often are the queries being run, how large the result sets are, how many queries are run in parallel. Is data being inserted and updated at the same time, how often; are there any triggers on the table.
For what we do, we have customized solutions for each DTS depending on why, when and which server and database is being updated.
October 14, 2004 at 4:41 am
Rebuilding an index optimises the index structure based on the data in the table at that time. A rebuild is a good idea after signigificant modifications of the kind you mention.
However.....the table & index structures will be exclusively locked for the duration of the rebuild on that table; this could takes several minutes or even hours on large tables so it's not a good idea do do it when the system is busy and rebuilding them every few hours seems like overkill (we rebuild ours every week).
UPDATE STATISTICS could be a reasonable compromise - look it up in Books Online.
October 14, 2004 at 7:12 am
The reason I have chosen to do it this way was based on the levels of fragmentation that was occurring after the data was being updated. The quickest solution that i had at the time was to use the "DBCC DBREINDEX" after the data was refreshed. I know it seems like overkill, which is why i am currently researching a more productive approach. I am currently going over the Update Statistics (Thanks for pointing me in that direction)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply