Click here to monitor SSC
  • Av rating:
  • Total votes: 45
  • Total comments: 5
Simon Sabin

What Specification Server Should I Buy?

13 August 2007

At the end of a presentation regarding SQL Server and hardware, I'm inevitably asked a question along the lines of:

"What specification of server should I use for running x?"

My answer inevitably starts like this:

"Well, it all depends..."

If people thought about the question a little more, they would probably realize how difficult it is to give any sort of sensible response. It's akin to asking a stranger how he or she thinks you should get to work. To offer you any advice would require answers to all of the following questions:

  1. Where do you work?
  2. Do you only work in once place?
  3. Do you have any preferences to forms of travel?
  4. When do you have to get to work?
  5. Do you have to go with someone else?
  6. Do you have to be able to get to work whatever the situation?
  7. What is your budget for travel?
  8. Do you have a limit on the amount of time you can travel?
  9. Do you like to use your travel time for other means? i.e. reading
  10. How long are you going to be working at this location?
  11. What forms of transport can you use?

In other words...it all depends.

In IT terms, all the above can be translated into the equivalent questions about hardware:

  1. What are you using your hardware for?
  2. Do you have different applications requiring hardware?
  3. What hardware can you use, do you have a preferred supplier, can you only use 1U servers?
  4. What response time do you want to get?
  5. Do you already have hardware you need to fit in with?
  6. Do you need to have redundancy in your infrastructure?
  7. What is your budget for hardware?
  8. How many users do you have?
  9. What different types of users do you have?
  10. What applications do you plan on using in the future?
  11. What skills do you have in your organisation?

Each question has a multitude of possible answers, resulting in a huge number of permutations. Even if a question had only four options, that's still one million permutations that you are expecting someone to process in a Q&A session or a presentation.

Of course, there will be some questions that you can't answer, so you'll need to make an educated guess. If you need a formula for specing out a database system, I would use the following:

Hardware spec = ((GB+ U) x S + DR)  x  1  
                                  DIQ                     BIQ 

BIQ = Business Knowledge of what they are doing in the next 3 years
DIQ = Knowledge and skill in the development of a performant/scalable system
GB = Volume of the Data
U = Number of users
S = Responsiveness of the system
DR = Requirement for DR

This equation dictates that,

  • The bigger the data, the bigger the system
  • The less skill the developers have, the bigger the system
  • The more users, the bigger the system
  • The faster the users want the system, the bigger the system
  • The less knowledgeable the business about what they want to do, the bigger the system

Furthermore, the following equation will help you estimate the amount you're likely to spend supporting the hardware,

Support Cost = 2(Calc/Act)

where:

Calc = Calculated cost of hardware
Act  = Actual spend on hardware

If you don't spend what you should up front, then you will end up spending more on support in the long run.

Put simply; it all depends. However, "it all depends" leave you no further down the line to an answer. So where do you start? Given that hardware is cheap (£3000 for a 8-way server with 8Gb of RAM), and employee time is expensive, here are my recommendations:

  1. Get as much memory as possible; you want as much of your database in memory as possible.
  2. Make sure you have fast enough disk, one transaction is at least one IO, so if you want 1000 transactions/sec you need 1000 IOs on your log.
  3. Make sure you have fast enough disk (I know I've already said it) so that when you can't do 1. you don't suffer too much
  4. If you are running a multi-user system, get as many processors as possible so you can process more queries at once.
  5. If you are using a SAN make sure you can monitor the performance of it; all the disks, the switches, everything. And don't share it with Exchange
  6. Make sure you write good code or points 1-5 won't make a difference if every query reads too much data, causes too much blocking and results in your multi-user system scaling to 1 user.
  7. Performance-test your application so you know how it breathes: How does it use CPU, memory and disk? Wembley Stadium might be great because it can hold 90,000 people but still takes ages to get out, because the roads and tube railway to it have hardly changed since 1930.

We have invested in HP DL585s in my current place, for ~£5k you get a 64 bit 4 way box with 8Gb of ram (storage not included). I'm currently looking at a workstation with 2 x Dual core with 16Gb, 5 SATA 146Gb drives for ~£2,500. I personally am going for the AMD dual core chips because you will be able to upgrade to quad cores when they come out. That provides a very easy and cheap upgrade path if CPU is a bottleneck.

I haven't discussed the issues of redundancy, high availability or supportability, which is touched on elsewhere on Simple-Talk in Database High-Availability: Soup to Nuts. Hopefully, I'll cover these points in another article.

Simon Sabin

Author profile:

Simon Sabin is a independent consultant specialising in database architecture. He has been working with SQL Server for almost 10 years, was awarded as a Microsoft MVP in April 2006 and is an active member of UK SQL Server Usergroup. You can read his ramblings on SQL Server on his blog, http://sqlblogcasts.com/blogs/simons.

Search for other articles by Simon Sabin

Rate this article:   Avg rating: from a total of 45 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: What should one buy for developers
Posted by: Phil Factor (view profile)
Posted on: Wednesday, August 15, 2007 at 9:30 AM
Message: I realise that this article is about the spec of production servers, but I reckon that the developers should have a database server that is as slow as one can get away with. There is nothing unkind about this. I do the same for myself. I develop database systems on a very slow conservative, but robust, system. Then, I can detect instantly, without fancy profiling tools when a query isn't optimised, or an algorithm isn't properly thought out. The same goes for test servers. We generally waste money on fancy kit for development servers when, in fact, such servers, like a good wine, improve with age.

I wouldn't agree the same principle for the developers desktop computer as I wouldn't want to crank up SSMS with anything less than a dual-core. Otherwise, one soon gets a profound sense of the meaning of eternity.

Subject: Don't forget SQL licensing!
Posted by: Joel Mansford (not signed in)
Posted on: Thursday, August 23, 2007 at 3:27 AM
Message: Since this is SQL Server related we must consider the chosen SQL Server licensing model.
Getting extra CPUs is all very well as long as you're not on a per-processor licence.
Note that currently the SQL Server per-processor license is per physical CPU, thus a quad-core CPU is cheaper on software than two dual-cores.
If you need a ton of cores but have few users then consider the per-user license.

Subject: transactions/sec
Posted by: Anonymous (not signed in)
Posted on: Thursday, August 23, 2007 at 9:28 PM
Message: How can I measure how many transactions/sec I have?

Subject: Development server
Posted by: Anonymous (not signed in)
Posted on: Tuesday, September 11, 2007 at 11:47 PM
Message: Depends on what you are developing.

I understand the desire to stress test in dev and for Business as Usual Components a low spec environment is adequate.

Developing and testing a High data Volume rarely executed components on a low spec machine can increase the time for a build-test iteration. Depends how expensive your developers time is compared to the hardware cost.

Not All developers are Performance blind, Stress testing is important but then again who wants to pay for the down time waiting for a testing environment data re population or a batch cycle to run ?

Anyone have any feeling for starting point
BIQ, DIQ values prior to re-estimating them after some experience with the client ?


Subject: HP
Posted by: Anonymous (not signed in)
Posted on: Saturday, December 22, 2007 at 4:19 AM
Message: http://www.4gumpa.com/index/systems/index.aspx?intro=HP%20Server

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.