Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Does anyone know of any upper limits to table size in SQL Server 2008. I have a friend who wants to hold about 900,000,000 records in one table. I don't know anything about the records in question, or how heavily it will be read/updated.

Obviously they will need to think about indexing and disk IO, but I wondered if anyone had experience with really big tables in SQL Server 2008 that they could share?

share|improve this question
add comment

3 Answers 3

up vote 3 down vote accepted

I have a database that has a single table with around 1.8 billion records (yes, a B not an M) in it right now. There's another table in the same DB with around 740 million records.

The total DB size is around 300GB.

The server is running just fine. It is a quad core Xeon with 32GB of RAM and a very very fast disk array.

As far as tips and tricks:

  1. Seriously think about why you want that much data. It is a LOT to deal with at any one time. Also, once it is set up forget about changing indexes, columns, or pretty much anything else. Those operations take too long to complete on tables this large.

  2. Seriously think about why you want that much data. Yes, I'm repeating myself. Find another way if possible. Recovering a DB of this size is a complete PITA and may take a long time... Like a day or so.

  3. If possible, rollup your data in some meaningful way. If using the data for trending, try and get your calculations down as soon as possible and throw away the underlying data. Of course, if the underlying data has to be kept for some reason, store it in a flat file or something else.

  4. Don't use any form of encryption with this DB size. If your data requires that, then plan on spending a lot of money on hardware (as in the $1M+ range).

  5. Take your time and test different recovery models.. Right down to hard booting the server in the middle of transactions. See what happens. I found that "simple" works best for me.

My data is mostly transactional, and, quite frankly, not mission critical. If the entire db suddenly fell on it's face, well, a few eye brows might be raised by nothing major.

The DB is servicing about 150 requests/second. 99.999% of which are writes. Nightly, we roll the information obtained up into summary tables. There is a small dashboard site which is used by maybe 5 people to view what the historical / daily summaries are.

What kind of information are you really trying to find?

share|improve this answer
    
Thanks for the info –  KiwiNige Nov 10 '09 at 4:03
add comment

This MSDN document says table size is "limited by available storage." I don't have enough SQL Server experience with big tables to give you any performance guidelines, but without knowing anything about the records in question or the read/write patterns, I don't think anyone else can help either.

share|improve this answer
    
Thanks for the link, that's pretty much all I need –  KiwiNige Nov 10 '09 at 1:16
add comment

By any standard, that's a lot of records. There exist many techniques for working with large amounts of data but... there is no possible way anyone can lend an opinion further without much more info on the problem your friend is trying to solve.

share|improve this answer
add comment

Your Answer

 
discard

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.