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:
- information accumulation about users activity.
The statistical information obtained this way may
be used both for the analysis of the safety system, and for obtaining information
about transactions fulfilled by users. - debugging of applications and stored procedures code.
After the code of the application or the stored
procedure is written it is necessary to trace what it will allow, not only
to make sure of its serviceability but also to observe all processes of its
execution, while simultaneously optimizing the code. - preventive monitoring in order to reveal problems in database server operation.
Search for errors in Transact-SQL queries (and stored procedures) code.
You may reveal certain problems connected with the operation of computer
components or obtain data necessary for the diagnosis of such problems.
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.
