Performance Monitoring Example: Identifying Bottlenecks

A bottleneck is a component of the system that restricts the performance of the entire system, for example, due to excessive demand on a system resource. Every system will have bottlenecks. By monitoring the Microsoft® SQL Server™ system for bottlenecks, you can determine if changes can be made to the limiting component to make it perform at an optimum level.

Demand on a resource can become extreme, causing a bottleneck for the following reasons:

Insufficient resources requiring additional or upgraded components.
Resources of the same type are not sharing workloads evenly and need to be balanced. For example, one disk is being monopolized compared to others.
Malfunctioning resource requiring replacement.
Incorrectly configured resource requiring reconfiguration.

Decide What to Examine

Low numbers can be just as meaningful as high numbers. If a number is lower than expected, it may be a clue to a problem in another area. For example:

Some other component is preventing the load from reaching this component.
Network congestion is preventing client requests from reaching the server.
A bottleneck is preventing client computers from accessing the server as frequently as expected.
Windows NT Performance Monitor is being used incorrectly (for example, user is neglecting to turn on disk counters, looking at the wrong instance or the wrong counters, or even looking at the wrong computer).

A low number could also mean that the system is performing better than expected in meeting user expectations.

These are five key areas you should monitor to track server performance and identify bottlenecks.

Bottleneck candidate	Effects on the server
Memory usage	An inadequate amount of memory allocated or available to SQL Server will degrade performance. Data must be continually read from the disk rather than residing in the data cache. Microsoft Windows NT® performs 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 your system to run too slowly or restrict the amount of memory that could otherwise be made available to SQL Server.
Blocking locks	A process may be forcing another process to wait, thereby stopping the blocking process.

Monitoring Memory Usage	SQL Server: Locks Object
Monitoring CPU Use	SQL Server: General Statistics Object
Monitoring Disk Activity

Performance Monitoring Example: Identifying Bottlenecks

Decide What to Examine

See Also