Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 100 million records?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 11/12/2013 :  10:09:16  Show Profile  Reply with Quote
I have a client with whom we are hosting his database on MSSQL Server 2008 R2 (MSSQL EXpress?) on a dedicated windows server with Quad Core and 12GB ram.

His intention is to create and issue 100 million promo codes. The codes are alaphanumeric.

My goal is to get a rough estimate of server specs needed for his endeavour.

My questions would be:

1. what sort of mssql storage space would be needed for 100 million records?

2. what sort of server spec would be suitable for searching a code from 100 million records? Should we look at Amazon cluster for example? Is the server we have powerful enough?

Any help or guidance is much appreciated.

Kindest Regards
Craig Edmonds

Almighty SQL Goddess

38200 Posts

Posted - 11/12/2013 :  12:33:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
Express edition? Will the database be under 10GB? You do know that it can only use 1GB of RAM and 1 CPU, right?

1. You haven't provided enough info. Show us the DDL for the table.
2. You haven't provided us enough info. 100 million records isn't all that big. Show us the queries. What does "searching a code" mean?

Number of records doesn't necessarily drive the hardware specs. It's how the utilization that matters. How many concurrent users will there be? Is it just that one table? Is it mostly reads? Will there be writes? What's the reads to writes ratio? What are the performance requirements?

Tara Kizer
SQL Server MVP since 2007

Edited by - tkizer on 11/12/2013 12:37:23
Go to Top of Page

Patron Saint of Lost Yaks

30421 Posts

Posted - 11/12/2013 :  14:17:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
100 million rows with a cap of 10GB data file means each row can be 100 bytes wide. It's quite a lot.
If you only need promo codes, I assume you are creating 100 million unique values with a random stepping to avoid "guessing" a promo code.
Make a unique clustered index on the promo code column and 1 core will probably suffice.
Will you have other columns like "ClaimDate", make use of MERGE statement to keep transactions to a minimum.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000