How many rows is it reasonable to put in SQL Server? (large data question)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How many rows is it reasonable to put in SQL...

How many rows is it reasonable to put in SQL Server? (large data question)

Rate Topic

Display Mode

Topic Options

Author

Message

Chris.C-977504

Posted Monday, April 16, 2012 11:53 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:10 PM
Points: 349, Visits: 256

First, I'm aware of the BOL answer that we are only limited by disk space, and I have reviewed the similar question posted in 2003 (on this site in the sql7/2000 Forum).

I have someone that has asked for my help migrating mainframe data to SQL Server (or Oracle or My Sql - but I know SSvr best).

There are currently 178 million records in the mainframe db.
This is a couple orders of magnitude larger than I'm used to dealing with.

Are any of you aware of any 'better practices' when it comes to large data?

Here's an example of what I'm looking for: a friend of mine once commented that he had 25,000 pdf documents to deal with and it was a pain to determine how to break them into three directories on the server.
Me: Can't one directory hold more than that?
Her: Yeah, Windows will handle n files in a directory, but you start to get lots of delays when you put more than 10,000 files in a single directory.

I've heard statements in the past like 'you can put millions of rows in ssvr, but if you're going to join tables with more than a million rows you really need Oracle on a VAX'. Even if that statement from an Oracle DBA in 2005 was true, I'm not sure that it holds true now.

Any insights? Thoughts?

I'm interested in statements from the "don't do what I did" category
and any "I already have an OLTP db with x rows and I do joins and it works fine, don't worry."

Thanks, sorry for the length - but I'm trying to avoid the rtfm answers and get some people that have real world experience to share their stories (or horror stories)
-Chris C.

Post #1284352

GilaMonster

Posted Monday, April 16, 2012 12:11 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 37,726, Visits: 29,985

Largest number of rows I've heard of in a single unpartitioned table - 100 billion rows.
Largest number I've personally worked with - 150 million rows

Make sure there's enough hardware (memory and IO bandwidth)
Make sure the disks are designed for throughput not size (chapter 2: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/)

Make sure you index sufficiently, but not too much. Index based on the queries that will run on the table.
Make sure your queries are written optimally.

Watch your maintenance times, large databases take longer to backup, restore, run integrity checks, do index rebuilds, etc.

I wouldn't sweat 200 million rows these days.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1284370

MysteryJimbo

Posted Monday, April 16, 2012 12:30 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:28 AM
Points: 1,125, Visits: 14,511

My largest system is currently running with 3 billion rows in single unpartitioned heap. It still works just fine as a backend to an extremely busy website.

In terms of database size, its growing by around 60-80GB a week and is currently at around 6TB.

A few hundred million rows these days isnt that much.

Post #1284391

ScottPletcher

Posted Monday, April 16, 2012 12:54 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 1,324, Visits: 1,778

178M rows shouldn't be a problem per se, if you index properly.

THE biggest factor is getting the correct clustered index.

Get that right, you should have no issues with that many rows on decent hardware.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1284417

george sibbald

Posted Monday, April 16, 2012 1:06 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:30 PM
Points: 5,269, Visits: 11,205

largest table I have had was 1.5 billion rows. That was fine on a server with only 4Gb of memory.

Indexing is very important, for instance I found it useful to separate out the non clustered indexes to a separate disk.

the table was 300gb, and thats more important than the number of rows, how much disk space does the table and its indexes use.

all of this does not mean you should not minimise table space if possible by eliminating fragmentation, archiving or partitioning if appropriate.

biggest problem with it was probably maintaining it (reindexing\integrity)

---------------------------------------------------------------------

Post #1284431

CapnHector

Posted Monday, April 16, 2012 2:11 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709

im looking at a table with close to 300 million records with proper indexes and have no problems. the key as people have stated is the proper clustered and non clustered indexes for the queries run on the table.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler

Post #1284501

« Prev Topic | Next Topic »

Permissions