Professional SQL Server® 2005 Performance Tuning

3.9. Typical Performance Problems

When investigating any kind of performance problem, it's important to first identify the area of concern, and in many causes your first step should be to rule SQL Server in or out of suspicion.

The following section contains an overview of some typical SQL Server poor performance scenarios, using some of the counters discussed to gain visibility of the problem and a better understanding of the causes.

3.9.1. Typical Disk Problems

Disk problems are the most common type of performance problem since SQL Server is so dependent on disk I/O to serve the databases. Lack of proper housekeeping, poor initial disk design, or database architecture can all worsen over time. Figure 3-1 shows a disk with two volumes, where data was stored on T: and logs stored on drive S:

Figure 3-1. Figure 3-1

Here you'll see excessive disk queue lengths on the data partition, which caused SQL Server to appear unresponsive at times. Once identified, the problem was rectified by moving the busiest database to an isolated partition, which allowed normal response times to resume.

3.9.2. Memory

Figure 3-2 shows a memory problem with SQL Server operating normally until it receives notification from Windows to relinquish memory. In this case, an extended stored procedure, called within SQL Server, caused the buffer pool to shrink. Here it would be relatively easy to identify ...