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

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

craigedmonds
Starting Member

Spain
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
www.craigedmonds.com

tkizer
Almighty SQL Goddess

USA
35952 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
http://weblogs.sqlteam.com/tarad/

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

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 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.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000