Professional SQL Server® 2005 Performance Tuning

6.1. Server Bottlenecks

There are three server resources that you should be concerned about when trying to identify and resolve bottlenecks. You should review them in the following order: Memory, I/O, CPU. This section looks at each of these in turn and you'll also learn why you should look at them in this order.

6.1.1. Memory

Memory very often drives the overall performance of a system and lack of it often creates a greater perceived performance impact than the lack of any other resource. Lots of spare memory can also mask bottlenecks in other areas so it's important to know how your memory is being used before looking at anything else. This is covered in Chapters 2 and 3.

6.1.1.1. Effect on I/O

Lack of physical memory has a greater effect on I/O than anything else because paging activity will be high, which means high disk activity. Obviously, when you notice your I/O throughput reducing in this scenario, the wrong approach to take would be to buy faster disks.

6.1.1.2. Effect on CPU

The goal for CPU usage is to spend as much time as possible executing in user mode. Kernel mode represents time spent working on system processes and not your application, so a lack of physical memory will cause high CPU utilization in kernel mode because the server has to work harder to manage what little memory it has. Again, the wrong approach to take would be to buy faster CPUs, but could be easily done if you only looked at CPU utilization in isolation. User mode and kernel mode are covered ...