13 August 2007

What Specification Server Should I Buy?

Simon Sabin explains why he is always cagey about giving advice on the sort of hardware to run SQL Server on, but admits to some general rules.

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.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 37085 times – thanks for reading.

Tags: , , , , ,

  • Rate
    [Total: 49    Average: 3.1/5]
  • Share

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.

View all articles by Simon Sabin

  • Phil Factor

    What should one buy for developers
    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.

  • Joel Mansford

    Don’t forget SQL licensing!
    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.

  • Anonymous

    transactions/sec
    How can I measure how many transactions/sec I have?

  • Anonymous

    Development server
    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 ?

  • Anonymous