Establishing a Performance Baseline
Applies To: Windows Server 2003, Windows Server 2003 R2, Windows Server 2003 with SP1, Windows Server 2003 with SP2
Baseline performance monitoring involves establishing a performance baseline for your system. A performance baseline includes a single performance chart accompanied by an interpretation of the results, based on your environment. Many elements of the chart, such as the timeframe, vary according to the environment. You can use System Monitor in Windows Server 2003 to establish your performance baseline. The System Monitor chart can be created in real time or based on a performance log file. It is recommended that you base your chart on a log file, because this allows you to record statistics for an extended period.
When establishing your performance baseline, you must select the sampling interval and the server from which to monitor. The sampling interval determines the amount of disk space that your baseline requires. (Using performance counters on the server does not add much overhead to the server running SQL Server.) If you find that your baseline data is taking up too much disk space, you can use a larger interval between data samples. The larger the sampling interval you use, however, the less accurate your graph will be.
You can monitor remotely; however, use of the counters across a network connection for an extended period of time can congest traffic on your network. If you have disk space on your server for the performance log files, it is recommended that you record performance log information locally.
Because of performance impacts related to the number of counters being sampled and the frequency with which sampling occurs, it is important to test the number of counters and the frequency of data collection so you can determine the balance that meets the needs of your environment while still providing useful performance information. For the initial performance baseline, however, it is recommended that you use the highest number of counters possible and the highest frequency available.
Table 5 lists the recommended performance counters to include in your performance baseline.
Table 5 Performance Counters for Baseline Performance Monitoring
| Object\Counter | Definition | Recommendations |
|---|---|---|
Memory\Pages/sec |
The number of pages read from or written to disk to resolve hard page faults. This counter serves as a primary indicator of the types of faults that cause system-wide delays. |
Although it is normal to have some spikes, this counter generally remains at or close to zero. |
Network Interface\Bytes total/sec |
Number of bytes traveling over the network interface per second. This counter only reflects the local network connection. |
If this value stays below 50 percent of your available network bandwidth, the network adapter on the server running SQL Server 2000 is not likely to cause any performance bottlenecks. |
PhysicalDisk\Disk Transfers/sec |
The rates of read and write operations on the disk. Define a counter for each physical disk on the server. |
|
PhysicalDisk\Avg. Disk Queue Length |
The average numbers of both read and write requests that were queued for the selected disk during the sample interval. |
The number of requests should not exceed two times the number of spindles constituting the physical disk. If the number of requests is too high, you can add additional disks or replace the existing disks with faster disks. |
SQLServer:Memory Manager\Total Server Memory |
The total memory in use by SQL Server 2000. |
Add memory to the server if this value is generally higher than the amount of physical memory in the server. |
SQLServer:Access Methods\Full Scans/sec |
The number of unrestricted full scans. These can either be base table or full index scans. |
|
SQLServer:Buffer Manager\Buffer Cache Hit Ratio |
The percentage of pages that were found in the buffer pool without having to incur a read from disk. |
When this percentage is high, your server is operating at optimal disk I/O efficiency. If this value decreases over time, you might consider adding physical memory to your server. |
SQLServer:Databases\Log Growths |
The total number of log growths for the selected database. |
Run against your application database instance. |
SQLServer:Databases Application Database\Percent Log Used |
The percent of space in the log that is in use. |
Run against your application database instance. |
SQLServer:Databases Application Database\Transactions/sec |
The number of transactions started for the database. |
Run against your application database instance. |
SQLServer:General Statistics\User Connections |
The number of users connected to the system. |
Research any dramatic shifts in this value. |
SQLServer:Latches\Average Latch Wait Time |
The average latch wait time, in milliseconds, for latch requests that had to wait. |
If this number is high, your server might have resource limitations. |
SQLServer:Locks\Average Wait Time |
The average amount of wait time, in milliseconds, for each lock request that resulted in a wait. |
|
SQLServer:Locks\Lock Waits/sec |
The number of lock requests that could not be satisfied immediately and required the caller to wait before the lock was granted. |
|
SQLServer:Locks\Number of Deadlocks/sec |
The number of lock requests that resulted in a deadlock. |
|
SQLServer:Memory Manager\Memory Grants Pending |
The current number of processes waiting for a workspace memory grant. |
For information about how to use System Monitor to create performance charts and logs, see “Monitoring performance” in Help and Support Center for Windows Server 2003.