Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm maintaining Somebody Else's Code. There's a stored procedure that's run once a day or week (depending on config), that migrates a bunch of records from an active table to an archive. But it's not a true archive, since there are several live processes that include the archive table in SQL queries, which is properly indexed for the purpose.

Now the history of this story is that the archive table grew and grew, and the archiving SP started taking a very long time. So Somebody decided it would be a good idea at the beginning of the SP to drop all the indexes on the archive table and recreate them at the end. Of course, recreating them also takes eons, but arguably not as long as inserting the new records with the indexes in place.

This situation just screams at me: "THIS IS SINFUL!" It just seems wrong, wrong, wrong to be screwing around with indexes at runtime. I'm sure this is not an uncommon situation, so what is the "correct" way of handling a situation like this?

share|improve this question
Dropping & recreating indexes is standard practice before & after significant ETL load jobs. – Phil Mar 14 '12 at 11:44
Dropping and recreating the whole indexes just because you are appending some additional records to the table doesn't seem a good strategy to me either. How large is the table? How many rows does the process add? What edition are you on? Is partitioning available (and if so is it suitable for your archiving pattern?). Is this process running out of hours or potentially whilst the live processes that include the archive table are running? – Martin Smith Mar 14 '12 at 11:44
Dropping and recreating the entire indexes for a growth of 0.1% seems like complete overkill to me unless this is running out of hours at a time when it doesn't really matter. – Martin Smith Mar 14 '12 at 12:01
@MartinSmith Agreed, doesn't seem right for such small amounts of data change/growth – Phil Mar 14 '12 at 12:10
@MartinSmith - Index rebuilds can be pretty quick. Often it's quicker to drop and rebuild indexes on staging tables then to do a large bulk load with the indexes in situ. If you've got a small increment it's probably quicker to leave the index in place, but I've seen indexes take a bulk load from 8 to around 30 minutes, and the index rebuld just take a few minutes. It was quicker to drop and rebuild the indexes. – ConcernedOfTunbridgeWells Mar 14 '12 at 13:58

Instead of dropping the indexes, I'd recommend disabling them then enabling them. Preferably through a cursor (ignoring the clustered index) so that if an index is added or removed from the archive table the stored procedure doesn't need to be modified.

This is pretty standard when doing large data warehouse loads, which is basically what you are doing.

UPDATE: Scrub that. With the very small workload that you are doing just insert the records without dropping and readding the indexes. If you were moving hundreds of millions of rows then removing the indexes would be worth it.

share|improve this answer
Did you read the comment thread that indicated that (1) There might be concurrent activity on these tables from other queries. (2) The number of rows inserted is about 0.1% of the size of the table? If not does that change things at all? – Martin Smith Mar 14 '12 at 19:34
No I didn't see the very small workload that he was talking about. – mrdenny Mar 14 '12 at 19:38
up vote 3 down vote accepted

I think the clue to the answer is in the remark in my question:

But it's not a true archive, since there are several live processes that include the archive table in SQL queries.

If the archive table is being actively used for queries, then it's not an archive, and the whole point of archiving records has been lost.

This whole issue comes back to a bad design decision. Originally the records were supposed be be archived by groups of completed data - in our domain, that's "orders" (as in orders placed by a customer). So when an order was completed, paid for, and no longer a going concern, it and all its related data would be archived. Then somebody came along and decided that we needed the ability to partially archive an order, since some orders had one or two items that dragged out for months but 99% of the order was complete, so they wanted to push that 99% into the archive. But in order to get all the information associated with that order, you'd need to go into the archive. So they created a view that straddles the live and archive tables for the shop-floor app to use. And of course, as soon as you do that, you've violated the concept of an archive, i.e. that it should be information that you don't need to query on a regular basis. And once you've done that, you inevitably start going down the path of kludges like this that dramatically affect the performance of your mission-critical software systems.

So in this particular case, I'm going to go down the route of fixing the original bad design decision, and stop with this nonsense of partial-archiving. Then the archive can do what archives are supposed to do (i.e. sit back and enjoy retirement), and leave the active queries working only on the active tables.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.