Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL etc

Posted Dec 19, 1998

Microsoft SQL Server performance monitoring with Microsoft Windows NT Server Performance Monitor

By Alexzander Nepomnjashiy

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:

  1. 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.
  2. 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.
  3. 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.

SQL etc Archives

Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM