• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

When to archive data

Roughly how many records would need to be present in a table before you would considder archiving some of that data?

500,000 records?
1,000,000 records?
10 million?

I have a current process that inserts data into about 5 different tables.  Most of the tables have < 300K records, but there is one table with over 1 million records.  The over all process takes anywhere from 5 minutes to 3 hours to run (regaurdless of the number of records being loaded).

I'm currently running some SQL traces to see exactly what step is taking the longest (probably some locking issue), but I thought I'd ask about the archiving anyway.

3 Solutions
Scott PletcherSenior DBACommented:
To me it's not so much about table size per se but about:

1) what historic data you need available to meet business requirements

2) what the system can handle *comfortably* (i.e. there's excess capacity available so the "excess" data isn't hurting the system anyway)

Also naturally the quantity and types of requests against that table are also a big factor.  But, in a practical sense, once the business no longer needs to use it -- or only extremely rarely or in very rare cases -- you can archive it (or just delete it, depending on the data).
i deal with about 2 million records (peanuts compared to a lot of people) i would never consider archiving anything.  Put your query's in QA and see what they are doing you will probally find the problem dosent stem from sheer amount of records.

There comes a time when users no longer need the data for production purposes. This will give you an idea of what to archive and when. This is also the time when data moves from production/transactional needs over to historical/reporting use.

A million records isn't a whole lot of records. If you find that you are drilling thru records that really aren't used (old) and removing the records would result in a significant performance improvement, then it's time to archive.

Also, if think that backups are taking way too long and want to reduce the admin time, then it might be time to archive.
_TAD_Author Commented:

Those were all pretty much my thoughts.  To sum up, the end users are experiencing some occasional performance issues.  Our VP, who is the original pointy-haired boss, heard that archiving data can have a great impact on performance.

So, instead of really trying to figure out what's wrong, I have to chase after his ideas and either prove or disprove them.

lucky me.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now