February 4, 2003 at 9:44 am
A bit of a newbie question for you...
I have a SQL Server ready to go in a production environment, where we expect some tables to pick up between 10,000 and 40,000 rows per year. Physical data storage isn't an issue (it's 'unlimited', we just get charged per GB).
Is there some rule of thumb that dictates when you should archive the oldest data to improve the performance of select statements and the DB in general? It's valuable for us to keep old data around as much as practical for historical reference (we often need to look up records from past years).
I would think that should depend on factors like the data types stored, indexes, etc. but I could use some experienced opinion here.
Thanks.
February 4, 2003 at 10:34 am
I am sure you will get a wide range of opinions here, but generally, if your indexes and storage structures are properly designed and maintained, you will not notice significant performance problems until you get into the millions (if not tens of millions) of records. If the storage requirements you described are accurate, I don't think you need to worry about archiving for quite some time. Time could be better spent ensuring data structures and index organization is as streamlined and efficient as possible.
February 4, 2003 at 12:32 pm
It's not so much the size of the data storage as the amount of processors and size of RAM.
We have one database with 192+ GB of data and it has several tables one of which has 556+ million rows.
Our queries are fast even with that amount of data. Why? We have 4 processors and 4 GB of RAM. Plus our data is properly indexed.
So, on the issue of how much data is too much? Consider how many processors and how much RAM you have. Then use that to help you determine how much data is too much.
-SQLBill
February 4, 2003 at 12:34 pm
BTW- that was only a years worth of data.
-SQLBill
February 4, 2003 at 2:18 pm
My suggestion is make sure like was said you have good indexes and have a good maintainence of the DB going on. However, wether or not you should archive is up to you. If you are paying per GB and have 40GB but only the last 1GB is every queried then by all means get rid of the data you don't need. The rulle is you get best performance with minimal data. So don't keep anything you don't need, archive it to somewhere else in case for auditing or other reason it might ever need to show up. But as for performance concerns with proper planning you can go on forever. It is just a matter of save yourself the most money you can with what you are discussing.
I would also make the suggestion since you state you often need to look up previous data that you setup a historical table to move out dated data to so you can minimalize the data for the shear performance of accessing data in a table. But again with good indexes you will not see too much with a max of 40000 rows a year. I have 4 databases that are 5 million records a peice, go back 2 years and contain nothing but call center switch data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply