What specification hardware do I need to run SQL Server for good performance?

A. (1) How long is a piece of string? (2) It depends.

These are the two most accurate answers that can be given, as complete information is needed on size of database, complexity, triggers, indices, DRI, number of users, transactions per minute, update ratio, row length, key sizes etc. before even a rough estimate can be provided. Plus hardware performance in terms of cpu power and disk performance is always increasing at phenomenal rates.

However, as some general pointers :-

Memory is the most important resource until you get to 95% cache-hit ratio. It then doesn't matter too much how much extra memory you add. Start with 128Mb ram (because ram is cheap), but make sure you have spare simm/dimm slots for expansion.

Next come disks and the disk subsystem - especially for updates. Battery-backed write-back cache is good - the more the better. The more raid controllers you have the better - not because they are generally a bottleneck, but because you can then have multiple sets of read and write-back cache assigned. Dedicating a controller just to the transaction log is good because this then gets some dedicated write-back cache.

Processors usually come last - SQL rarely gets processor bound except on the biggest systems, but if you have the money then get 2 or more. The most benefit comes from the 2nd with the law of diminishing returns kicks in after that. SQL scales well to 4 processors, and scales reasonably well to 8 (depending on the hardware implementation), but after that......... Remember that with 6.5 and below a single query will only ever use a single processor, whereas in SQL 7 it will parellise queries if it thinks that will make it run faster.

Don't forget network cards and network design. You won't get 500 concurrent users through a single 10Mbit Ethernet ISA card. If you have a significant workload in terms of users or large result sets then make sure you have multiple network cards - PCI bus master variety. Have the cards on different rings/segments if your users are devolved that way - otherwise attach the card(s) direct to a switch for full-duplex throughput and consider the use of either software or hardware card aggregation (like Raid for disks, but for cards). Some cards have this facility built-in to the hardware, but there are 3rd party software drivers that will make it work for any card.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.