Microsoft SQL Server & Solid State Accelerators - Technology Information

Computer Technology Review, May, 2001 by Craig Harries

Generally, people in the software industry like to look to software for solutions. Many experienced DBAs, this author included, feel a sense of defeat if the application cannot be made to perform suitably within the constraints of the existing hardware configuration.

This attitude is silly, of course. You can tune your application within limits, but you can't overcome hardware limitations with software tuning. Sometimes the software does point to hardware limitations but many times an experienced DBA just has to know when to stop tinkering with the software and look at hardware factors.

The process of tuning is a process of identifying and eliminating bottlenecks. By definition, you will always have bottlenecks that limit your application's bandwidth. Tuning is the process of successfully shifting bottlenecks to places that have a wide enough bandpass to handle your data throughput needs. Sometimes you need a solution to a database performance problem that can't be resolved through software.

The traditional reaction to a perceived hardware problem is to buy additional hardware-- most commonly memory and/or CPUs. I have been in a variety of shops that purchased additional CPUs (4 to 8 processors), or more memory (4GB to 8GB), and not seen a significant improvement in performance. Often, adding CPUs is a knee-jerk reaction to a perception of insufficient user throughput. More often than not, adding memory is perceived as the panacea for I/O problems, with the idea that more memory permits more caching which improves performance.

There are a number of reasons why this might be the case, but all boil down to this: If you buy memory or CPUs to solve an I/O problem, you are likely to be wasting your money. (See Mike Pluta's white paper, "The Tragedy of throwing Memory at an I/O Problem," available at www.imperialtech.com/tragedy.htm) To summarize this insightful paper, adding memory may alleviate symptoms, on the surface, but it's really masking rather than solving the underlying problem. To make matters worse, whenever the server or operating system decides that it needs that memory for other things, your bottleneck gets shifted, suddenly and unpredictably. In fact, it may be shifting back and forth many times in a short interval, making tracking down the bottleneck difficult.

The trick is to solve the specific bottleneck, so those individual problems get resolved and are scalable, so that as your processing needs increase, you can use the same (or comparable solution) to resolve future bottlenecking issues. That is to say, once you identify a problem, you want to solve that problem, not hide it.

In summary, there are two basic reasons to choose a hardware solution to a performance problem.

1. You have specifically identified a hardware bottleneck, in which case you should be choosing a hardware resource that addresses that problem (processor, memory, or Solid State Technology).

2. You have identified an application problem, and have determined that you are short on time or resources to correct it (alternately, that hardware will solve the problem less expensively in the short run).

This remainder of this article focuses on using Solid State technology to resolve I/O performance bottlenecks.

Identifying Your Hardware Bottleneck

The easiest way to understand your hardware problem is to use the MS-provided tools, specifically Performance Monitor. Using Performance Monitor, we can identify issues in each of a set of performance objects. The specific contents of the performance objects will differ based on the version of NT/Windows you are running.

Regardless of your operating system version, you will want to monitor your physical disks (or disk suites). Note that monitoring the disks will create some system overhead. Historically, this has been an increase of 3-5% of CPU, but with later versions of NT/Windows 2000 this monitoring seems to have a lesser impact. You'll have to turn it on specifically...at a command prompt, type diskperf -y. Then, reboot. Be sure to turn it back off when you're done with your hardware performance monitoring (and, reboot!).

Each disk on your system will have a separate disk object. This gives you the ability to understand, for each object, the frequency at which it is being accessed.

For each physical disk object, you can look at % disk time, which will tell you how busy the disks are. Note that you may be hardware bound before you hit 100% busy (See Fig).

In the figure we're seeing a few spikes, but in general the sampling points are showing very low output. If you are seeing a line across the top, rather than across the bottom, you have an I/O device that is not keeping up with the system requests. In general, if you are above 70-75% utilization regularly, you do not have sufficient capacity to handle peak data surges.

Additionally, check out your Avg. Disk Queue Length. This should always be less than 1.0 and usually is zero. As this average queue length number increases, disk contention increases. Contention means that the system is waiting while another I/O is accessing the disk. Occasional waits are normal, but if this is a steady thing, you have an I/O bottleneck.