Professional SQL Server® 2005 Performance Tuning

3.3. Types of Bottlenecks

A bottleneck is any resource restricting database performance. The purpose of performance troubleshooting is to identify any resource causing a bottleneck and resolve it. For example, with a server running with 100 percent CPU utilization, you could say there was a CPU bottleneck.

Bottlenecks are many and various. Often performance troubleshooting is an iterative process whereby a number of problems are identified and resolved before an optimal configuration is determined. You should be working toward a database server that provides acceptable performance and is balanced in terms of resource utilization (that is, there isn't one resource that is completely exhausted while others have spare capacity).

Bottlenecks are often grouped into three areas: Memory, Disk, and CPU. These are listed in order of likely occurrence with SQL Server implementations. SQL Server is dependent on all of these components to perform well. However, it is especially reliant on memory to hold data pages that have been read from or are waiting to be written to disk. A lack of memory can severely impact performance as SQL Server may be forced to read data pages from disk (instead of memory, which is much faster). In some low-memory situations, SQL Server may be paged to disk by Windows. Again, this will cause performance to suffer as disk access is much slower than memory access.

Almost as important as memory, a healthy disk subsystem is required to allow SQL Server to read and write ...