100 million records? - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

100 million records?

Author

Topic

craigedmonds
Starting Member

1 Post

Posted - 2013-11-12 : 10:09:16

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

38200 Posts

Posted - 2013-11-12 : 12:33:51

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/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-11-12 : 14:17:20

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources