Hardware Guidelines | Inside Microsoft SQL Server 2000

Because SQL Server runs on any hardware that runs Windows NT or Windows 2000, you can choose from among thousands of hardware options. Although SQL Server Personal Edition runs on any hardware that runs Windows 98, your production applications probably will be deployed on Windows NT or Windows 2000. Therefore, the following discussion of hardware focuses on the needs of SQL Server running on Windows NT and Windows 2000 only.

Use Hardware on the Windows Hardware Compatibility List

If you cobble together a system from spare parts, Windows NT and Windows 2000 will run just fine, but your system might be unreliable. Unless you're a hobbyist and you like to tinker, this method isn't suitable for a production system. Support is another issue. Many people buy motherboards, chassis, processors, hard drives, memory, video cards, and assorted other peripherals separately and put together terrific systems, but the support options are limited.

Keep in mind that even name-brand systems occasionally fail. If you're using hardware that's on the Windows Hardware Compatibility List (HCL) and you get a "blue screen" (Windows NT and Windows 2000 system crash), you're more likely to be able to identify the problem as a hardware failure or find out, for example, that some known device driver problem occurs with that platform (and that you can get an update for the driver). With a homegrown system, such a problem can be nearly impossible to isolate. If you plan to use SQL Server, data integrity is probably a key concern for you. Most cases of corrupt data in SQL Server can be traced to hardware or device driver failure. (These device drivers are often supplied by the hardware vendor.)

NOTE
Over the life of a system, hardware costs are a relatively small portion of the overall cost. Using no-name or homegrown hardware is penny-wise and pound-foolish. You can definitely get a good and cost-effective HCL-approved system without cutting corners.

Performance = Fn (Processor Cycles, Memory, I/O Throughput)

System throughput is only as fast as the slowest component: A bottleneck in one area reduces the rest of the system to the speed of the slowest part. The performance of your hardware is a function of the processing power available, the amount of physical memory (RAM) in the system, and the number of I/Os per second that the system can support.

Of course, the most important aspect of performance is the application's design and implementation. You should choose your hardware carefully, but there's no substitute for efficient applications. Although SQL Server can be a brilliantly fast system, you can easily write an application that performs poorly—one that's impossible to "fix" simply by "tuning" the server or by "killing it with hardware." You might double performance by upgrading your hardware or fine-tuning your system, but application changes can often yield a hundredfold increase.

Unfortunately, there's no simple formula for coming up with an appropriately sized system. Many people ask for such a formula, and minicomputer and mainframe vendors often provide "configuration programs" that claim to serve this purpose. But the goal of those programs often seems to be to sell more hardware.

Once again, your application is the key. In reality, your hardware needs are dependent on your application. How CPU-intensive is the application? How much data will you store? How random are the requests for data? Can you expect to get numerous "cache hits" with the most frequently requested data often being found in memory, without having to perform physical I/Os? How many users will simultaneously and actively use the system?

Invest in Benchmarking

If you're planning a large system, it's worthwhile to invest up front in some bench-marking. SQL Server has numerous published benchmarks, most of them Transaction Processing Council (TPC) benchmarks. Although such benchmarks are useful for comparing systems and hardware, they offer only broad guidelines and the benchmark workload is unlikely to compare directly to yours. It's probably best to do some custom benchmarking for your own system.

Benchmarking can be a difficult, never-ending job, so keep the process in perspective. Remember that what counts is how fast your application runs, not how fast your benchmark performs. And sometimes significant benchmarking efforts are unnecessary—the system might perform clearly within parameters or similarly enough to other systems. Experience is the best guide. But if you're testing a new application that will be widely deployed, the up-front cost of a benchmark can pay big dividends in terms of a successful system rollout. Benchmarking is the developer's equivalent of following the carpenter's adage "Measure twice, cut once."

SEE ALSO
For information on TPC benchmarks and a summary of results, see the TPC home page at http://www.tpc.org.

Nothing beats a real-world system test, but that's not always practical. You might need to make hardware decisions before or while the application is being developed. In that case, you should develop a proxy test. Much of the work in benchmarking comes from developing the appropriate test harness—the framework used to dispatch multiple clients that can run the test programs simultaneously, to run the test programs for an allotted time, and to gather the results.