Lesson 1: Developing a Performance Monitoring Methodology | MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228

3 4

Before you begin monitoring SQL Server 2000 performance, you must develop a performance monitoring methodology. In this lesson, you will learn to establish performance monitoring goals. You will learn what activities and resources are important to monitor. Finally, you will learn how to monitor effectively.

After this lesson, you will be able to

Establish appropriate and effective monitoring goals
Determine which activities and resources to monitor
Develop a long-term monitoring strategy

Estimated lesson time: 15 minutes

Establishing Monitoring Goals

Before you begin monitoring SQL Server 2000 performance, you should determine your monitoring goals. You must decide what performance characteristics to monitor and how to monitor them effectively. The first step in performance monitoring is to understand the fundamental performance goals of any SQL Server installation.

User perspective—To minimize the response time for each query submitted by each user. Response time is defined as the length of time required for the first row of a result set to be returned to the user. This provides visual confirmation that the submitted query is being processed.
Server perspective—To maximize the total server throughput of queries submitted by users. Total server throughput is defined as the total number of queries handled by the server during a given period of time.

Many steps are involved in achieving these performance goals. These steps include providing adequate hardware resources, properly designing the database to avoid excessive blocking, and writing applications that submit efficient queries. As a database administrator, you generally do not have control over all performance factors. However, you do have the monitoring tools to determine and isolate the source of performance problems. Once you understand the source of performance problems, you can determine the best course of action to take to improve query response time and server throughput. For example, if the cause of poor performance is a poorly designed database that is slowed by excessive blocking, throwing more hardware resources at the problem will provide minimal performance benefit. Similarly, if you determine that the problem is either a congested network or an inefficient client application (such as one that generates excessive data roundtrips between the client and server), improving the hardware resources on the server will not resolve the problem.

Identifying Performance Bottlenecks

To improve the performance of your SQL Server 2000 installation, you must first identify the cause of the performance bottleneck. A performance bottleneck is defined as a performance-limiting condition caused by excessive demand on a system resource or database object. A performance bottleneck also causes underutilization of other system resources or database objects. Inadequate hardware resources, such as memory or processor, are common causes of bottlenecks. You can generally solve these problems either by adding additional hardware resources or by moving some of the load to other servers. You can generally detect a hardware resource bottleneck by observing excessive use of one or more hardware resource. Excessive processor use does not necessarily indicate the need for more processor resources. Other factors can also cause excess usage. These include inadequate memory, which causes hard disk paging, or inefficient queries, which can generate excessive processor use.

Low numbers can mean that the system is performing better than expected, but they can also reveal a performance bottleneck. For example, if query response times are low and the hardware resources on your SQL Server 2000 computer are not overworked, you must look beyond hardware resources on the server itself. The problem could be a slow network or congestion preventing queries from reaching SQL Server 2000, inefficient queries, or a poorly designed database. Performance monitoring is the process of identifying performance-limiting factors, so they can be resolved.

Solving one performance bottleneck can reveal another performance bottleneck. For example, if you solve an I/O bottleneck by adding an additional hard disk, you might then find you have a processor bottleneck or a need to further optimize some queries. At some point, the incremental performance gain from solving a bottleneck will not be worth the time or cost. For example, the cost required to improve query response time by 15 percent might not be cost-effective if users consider the current query response time acceptable. However, what is acceptable today can change over time as the number of users increases and the database grows. You might need to optimize queries that were efficient with fewer users and plenty of hardware capacity as database utilization changes.

Determine Trends

When monitoring SQL Server 2000, you must gain an understanding of the normal range of values for various counters. This enables you to detect the onset of problems and take corrective actions before they become critical. You should establish an evolving performance baseline. This involves recording an initial performance baseline using a number of different monitoring tools. This will help you understand how various aspects of your system perform under normal production loads and before any problems arise. You should update this baseline periodically using the same monitoring methods and definitions. Significant changes should be analyzed.

Recording and monitoring the same type of information over time enables the database administrator to recognize values that are far above or far below normal. The cause of abnormal values will generally reveal the onset of a problem that warrants additional investigation. Maintaining an evolving baseline assists the database administrator in determining when additional hardware resources are needed, additional indexes should be created, or frequently run queries need to be optimized. An application or a database design that is efficient for a certain number of users can become inefficient as more users increase competition for server resources. They can also remain efficient, but simply require additional hardware resources to handle the increased load. Regularly monitoring the trends will help you to determine the areas in which you will need to concentrate time and resources.

Note

Using an evolving performance baseline also assists the database administrator in determining peak hours and off-peak hours. This is useful for scheduling maintenance tasks.

Determining Resources and Activities to Monitor

Factors to monitor that affect SQL Server 2000 performance include hardware resources, network traffic, database design, and client applications.

Hardware resources might be inadequate for the load on the server—for example, insufficient processor resources causing processor queuing.
Competing server applications on the SQL Server 2000 computer might be using excessive resources—for example, running Microsoft Exchange Server and Microsoft SQL server on the same computer.
Hardware resource use might be unbalanced—for example, one disk being overused while another disk is underused.
A hardware resource might be malfunctioning—for example, a disk beginning to fail causing excessive retries.
General network congestion might occur—causing connection failures and excessive retries.
Improper use of cursors or ad hoc queries—transferring excessively large amounts of data between client and server, which might only appear as a problem over a slow connection.
Poor database design—resulting in excessive blocking locks. A blocking lock is a lock on a resource (such as a row or a table) held by a process that prevents another process from accessing the same resource until the first process releases the lock.
Poorly written applications—resulting in deadlocks. A deadlock occurs when a process holds a lock blocking a second process and the second process holds a lock blocking the first process. When an instance of SQL Server 2000 detects a deadlock (through the use of an algorithm), it will terminate one of the transactions, allowing the other to continue to process.

Isolating the Problem

Determining the source of a problem is generally a process of using one or more monitoring tools to identify the symptoms of the problem. Once you identify the symptoms, you must then perform additional monitoring tasks to obtain more specific information to isolate the source of the problem. In Lesson 2, you will learn about each monitoring tool and its capabilities. In Lesson 3, you learn to perform various monitoring tasks to assist in isolating performance problems with your SQL Server 2000 installation.

Lesson Summary

The goal of performance monitoring is to maintain and improve the overall performance and efficiency of your SQL Server 2000 installation. This consists of improving response times and server throughput. Maintaining an evolving performance baseline enables the database administrator to detect patterns of change before serious problems occur. Solving performance problems in-volves identifying and isolating performance bottlenecks to determine the areas in which to concentrate resources and most effectively improve performance.