SQL Server 2005 can be installed on a Windows 2000 or Windows 2003 server computer. A database administrator is always concerned about the performance of the SQL Server database engine and the server computer. Database Administrators monitor the performance of the server using various tools to analyze performance and resolve performance issues.
System Monitor: System Monitor is a tool used to monitor the performance of the server. It gives information about the resources that are under pressure. The values of various counters in System Monitor indicate which resource is under pressure. Performance deterioration can be diagnosed by setting performance alerts. These alerts show the increase or decrease in a counter value with respect to the pre-defined value. Normally the counters are monitored for a period of 24-hours. If an error occurs, a message regarding the error can either be sent to the administrator or written to the Application log. Log files can be saved in various formats such as text file, binary file, or SQL database file.
The counters that are to be measured in order to resolve performance issues are as follows:
- Memory: Pages/sec
- Memory: Available Bytes
- SQL Server: Buffer Manager: Buffer Cache Hit Ratio
- Physical Disk: Disk Reads/sec
- Physical Disk: Disk Writes/sec
- Physical Disk: %Disk Time
- Physical Disk: Avg: Disk Queue Length
- Physical Disk: % Free Space
- Logical Disk: %Free Space
- Processor: %Processor Time
- System: Processor Queue Length
- Network Interface: Bytes Received/sec
- Network Interface: Bytes Sent/sec
- Network Interface: Bytes/sec
- Network Interface: Output Queue Length
- SQL Server: General: User Connection
Performance counters related to memory usage: The memory used by SQL Server can cause performance issues if bottlenecks occur due to high memory consumption or high I/O operations. The counters that are to be monitored to detect and diagnose memory bottlenecks are as follows:
- Memory: Pages/sec: The Memory: Pages/sec counter is used to measure the number of pages that are paged out from Random Access Memory (RAM) to the virtual memory on the hard disk. The more the paging is, the more the I/O overhead will be on the server. The average counter value should be between zero and twenty. If the value is above twenty, the server will experience a memory bottleneck.
- Memory: Available Bytes: The Memory: Available Bytes counter measures the amount of free physical memory on the server. The average value of the counter should be greater than 5MB, otherwise the server will experience a performance hit due to memory pressure.
- SQL Server: Buffer Manager: Buffer Cache Hit Ratio: The SQL Server: Buffer Manager: Buffer Cache Hit Ratio counter value depends on the applications running on the server. These applications can be OLTP (Online Transaction Processing) or OLAP (Online Analytical Processing). If the server is running OLTP applications, the counter value should be between 90% and 99%. If the counter value is less than 90%, it indicates pressure on RAM. In order to resolve the issue, RAM should be increased.
- Physical Disk: Disk Reads/sec and Physical Disk: Disk Writes/sec: The values of the Physical Disk: Disk Reads/sec and the Physical Disk: Disk Writes/sec counters are difficult to interpret because the explicit transfer rate limits depend on the hardware installed on the server. These counters should be measured in conjunction with the values of the Memory: Available Bytes and the SQL Server: Buffer Manager: Buffer Cache Hit Ratio counters to detect memory pressure.
Performance counters related to disk usage: The performance counters related to disk usage measure the values of physical and logical disk counters. These counters detect and indicate disk bottlenecks. The counters used to detect disk pressure are as follows:
- Physical Disk: %Disk Time: The Physical Disk: % Disk Time counter is used to measure I/O bottlenecks on a physical disk array. It is not used to monitor the logical partition or the individual disks in the array. If the value of this counter exceeds 55% in a 24-hour monitoring period, it indicates that the server might be experiencing a disk I/O bottleneck.
- Physical Disk: Avg: Disk Queue Length: The Physical Disk Avg: Disk Queue Length counter is used to measure the pressure on a physical disk array. This counter is used to monitor individual disks in an array. If the value of this counter is eight in an array of four disks, the server might be experiencing a disk I/O bottleneck.
- Physical Disk: %Free Space and Logical Disk: %Free Space: These counters are used to measure the free space on the physical disk and logical disk partitions. If the value is less than 15%, an alert is triggered for disk errors.
Performance related to processor usage: The values of the performance counters related to the processor indicate the pressure on the processor resources. The counters used to monitor processor usage are as follows:
- Processor: %Processor Time: This counter is used to measure the utilization of a processor. If the value of the counter exceeds 80%, it indicates a processor bottleneck. It can be resolved either by reducing the load on the processor or by checking the database and its query performance.
- System: Processor Queue Length: This counter is monitored along with the Processor: %Processor Time counter to measure the utilization of a processor. If its value is more than two, it indicates a processor bottleneck.
Performance counters related to network usage: The values of Network Interface counters measure the number of bytes sent or received over a TCP/IP connection. No pre-defined values have been set for these counters. A sudden increase in the network traffic indicates an external attack. The counters used to measure the network traffic are as follows:
- Network Interface: Bytes Received/sec: This counter measures the number of bytes received over network adapters. A sudden increase in the value of this counter indicates an external attack.
- Network Interface: Bytes Sent/sec: This counter measures the number of bytes sent over network adapters. A sudden increase in the value of this counter indicates an external attack.
- Network Interface: Bytes/sec: This counter measures the number of bytes sent or received over network adapters. A sudden increase in the value of this counter indicates an external attack.
- Network Interface: Output Queue Length: This counter shows that data packets are in queue. If the value of this counter is more than two, it indicates a network bottleneck.
Performance counter related to user connections: User connections can cause performance issues in SQL Server 2005. The counter used to monitor the user connections is as follows:
SQL Server: General: User Connection: This counter shows the value of user connections. If the value of this counter is more than 255, it indicates a bottleneck.
