| Previous | Table of Contents | Next |
Performance tuning is based on finding your bottleneck (the item causing a performance limitation) and then doing something to correct the situation. The primary goal with performance tuning is to provide adequate system performance. And this basically falls into four categories: processor, memory, disk subsystem, and network. If these components are performing well, your users will generally not complain to you about system performance. In this section, we’ll look at each of these areas in more depth, using the Performance Monitor. We’ll use sample Performance Monitor workspaces and sample applications to illustrate the process of finding bottlenecks in these areas. If you are unsure how to use the Performance Monitor, Chapter 4, “Introducing The Performance Monitor,” covers the basic functions you will use with these examples.
Note: The example Performance Monitor charts, alerts, reports, logs, and workspace files, along with executable copies of the applications, can be found on my Web site at www.nt-guru.com/books/coriolis.
One thing to consider about performance bottlenecks is that when you find and solve one bottleneck, it will always expose another bottleneck. Let’s look at an example illustrating this point.
Let’s say you have a server, say one with a 133MHz Pentium processor, 32MB of main memory, a really fast pair of EIDE disk drives, an EIDE disk controller, and a 16-bit network adapter. Your primary concern is providing a fast user response time for shared files and SQL Server access. Where do you think the performance bottleneck would be? The processor? Memory? The disk subsystem? The network adapter? Well, based on my experience, it would turn out to be the disk subsystem. This would be caused by the EIDE controller. The EIDE controller would use the standard ATAPI driver provided with Windows NT Server. And this driver can only support one pending I/O request at a time. It’s not really the fault of the driver, though. Rather, it is the hardware it supports. Almost all IDE (and EIDE) disk controllers use programmed I/O (or PIO). A PIO controller uses the processor to move data from the disk, through the disk controller, to system memory. This requires a high percentage of processor time and limits the device to one I/O request at a time.
You could improve performance a bit by checking the manufacturer’s Web site for a bus master device driver replacement for Windows NT. A bus master controller will move data from the disk to system memory without processor intervention. This process is called direct memory access (DMA). Many manufacturers provide a bus master driver for their motherboards that include built-in EIDE controllers; however, this may not solve the problem in its entirety.
A better solution would be to replace the EIDE disks and controller with SCSI disk drives and a SCSI bus master controller; although, even here, you need to exercise good judgment. Don’t choose an 8-bit or 16-bit SCSI controller if you have a 32-bit expansion bus. Both of these will require copying data from the disk drive to memory below 1MB for an 8-bit controller and below 16MB for a 16-bit controller. This data will then have to be copied to the application or system buffer above 16MB, if you have more than 16MB installed in your system. This copying of data is referred to as double buffering, and it can severely hamper system performance. Instead, choose a 32-bit SCSI controller. The basic idea is to pick a disk controller with the widest I/O bus that your expansion bus supports for the fastest data transfer rate.
Now that you have increased the performance of your I/O subsystem and gained performance increases compared to the base system, another performance bottleneck might be exposed. This would most likely be the processor. SQL Server can be very processor intensive. Adding an additional Pentium processor and dedicating this processor to SQL Server processes will increase SQL Server performance. And once this has occurred, you’ll probably find that replacing your network adapter with a 32-bit network adapter and adding additional memory could increase performance even more. And this can go on and on—literally, forever. Eventually, you have to draw the line based on your budget and your acceptable performance requirements.
| Previous | Table of Contents | Next |