Microsoft SQL Server performance monitoring with Microsoft Windows NT Server Performance Monitor
December 19, 1998
Included in the wide range of responsibilities for database server administrators, (besides installations, set-up and configuration, troubleshooting and user support), are tasks such as support and control of the database server performance. Correctly planned controls reveal problems at the stage of their origin and allow the administrator to take measures toward their resolution. It's common knowledge that it's much easier to prevent problems, than to fight their consequences.
Performance monitoring evaluates the efficiency of the organization of your server as well as the construction of database applications. Based on monitoring results it's possible to rebuild applications and system options to achieve peak efficiency and to avoid bottlenecks. A bottleneck is a situation in which the general performance of the system is restrained by one of the hardware components of the server, which works on limited possibilities.
Let us suppose that your database server has a SCSI disk subsystem, a powerful Pentium3 processor, but only 64 megabytes RAM. In this case, the RAM is the bottleneck of the system; the performance in this case is reduced because the system has to work with a hard drive, exchanging data between the RAM and a swap file. Even with the replacement of components, (processor, hard drives), for a more powerful and productive environment, the general increase of performance will not be noticeable until you increase the RAM volume. On the other hand, even with sequentially increased accessible RAM volume, improvement of productivity will eventually be slowed down by other bottlenecks. It is quite possible that in this case the processor will have to be replaced or a faster hard drive (with a greater spindle frequency of rotation), will need to be installed in the system. Replacement of the components ends with the appearance of the next bottleneck in the system and so ad infinitum.
There is a consensus of opinion that it is impossible to achieve ideal performance, however it is quite possible to achieve a system with a performance that satisfies both you and your users. To achieve optimal performance you'll need to satisfy two conditions: 1) to reduce the response time of the system and 2) to increase capacity. The response time is the time slice between the system obtaining the user's query and returning to the user the first line of a resulting set. Capacity is the number of queries which the server is able to process during the defined period.
To analyze system performance, in order to reveal bottlenecks, it is necessary to possess certain statistical material. For this purpose, it is necessary to create a performance template (performance baseline). You then observe the system during periods of typical activity, taking readings of meters in defined time intervals.
It is desirable to create a separate template for each object of the system--a processor subsystem, a RAM subsystem, hard drives and network interfaces. The material obtained can be used both for the operational analysis of the concrete server, and for a comparison of congestion across all of the servers in the company. Based on given templates it's possible to make a decision to upgrade the server, or reallocate its load between other servers.
For example, databases that are often used may be on one server, while an identical server, (identical as far as hardware performance), sits idle since the databases placed on it are very seldomly used. In this case it is necessary to transfer part of the databases, and to switch part of users' calls to the second server to balance the load of both servers.
Before you begin to monitor your database server, it is necessary to define which tasks will be required to fulfill your needs. Besides performance control, there are other methods of monitoring with their own specifics and methods of realization. For Example:
During the planning stage it is necessary to define the appearance of the events you will watch. You need to select not only the general direction of monitoring, but also concrete objects, observation of which will allow for the completion of set tasks. It is also necessary to decide what information, related to selected events, needs to be collected. So, if the activity of a disk subsystem is to be monitored--it is necessary to collect information on the number of "read/write" operations, the number of these operations per second, and the queue lengths to disk to be serviced. Having placed appropriate filters, it is then possible to begin collecting the performance information.
It is not necessary to analyze the arriving information immediately. Meter results can be saved in special files for subsequent analysis. Some monitoring devices, (for example - SQL Server Profiler), allow you to play back a sequence of events, using the information contained in such a file.