Administering SQL Server
Identifying Bottlenecks
Bottlenecks are caused by excessive demand on a system resource, and they are present in every system, to varying degrees. By monitoring the Microsoft® SQL Server™ system for bottlenecks, you can determine whether changes can be made to the limiting component to make it perform at an optimal level.
Reasons that bottlenecks occur include:
- Insufficient resources, requiring additional or upgraded components.
- Resources of the same type that do not share workloads evenly (for example, one disk is being monopolized).
- Malfunctioning resources.
- Incorrectly configured resources.
Analyzing Bottlenecks
When analyzing event data, low numbers can be just as meaningful as high numbers. If a number is lower than expected, it may indicate a problem in another area. For example:
- Some other component may be preventing the load from reaching this component.
- Network congestion may be preventing client requests from reaching the server.
- A bottleneck may be preventing client computers from accessing the server as frequently as expected.
- System Monitor (Performance Monitor in Microsoft Windows NT® 4.0) may be employed incorrectly. For example, if you have not turned on the disk counters, or you are looking at the wrong instance, the wrong counters, or at the wrong computer, event data numbers may appear inexplicably low.
A low number also can mean that the system is performing better than expected.
These are five key areas to monitor when tracking server performance and identifying bottlenecks.
| Bottleneck candidate | Effects on the server |
|---|---|
| Memory usage | Insufficient memory allocated or available to SQL Server will degrade performance. Data must be read from the disk continually rather than residing in the data cache. Windows NT 4.0 and Microsoft Windows® 2000 perform excessive paging by swapping data to and from the disk as the pages are needed. |
| CPU processor utilization | A constantly high CPU rate may indicate the need for a CPU upgrade or the addition of multiple processors. |
| Disk I/O performance | A slow disk I/O (disk reads and writes) will cause transaction throughput to degrade. |
| User connections | An improperly configured number of users can cause the system to run slowly or restrict the amount of memory otherwise available to SQL Server. |
| Blocking locks | A process may be forcing another process to wait, thereby slowing down or stopping the blocking process. |