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.
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.
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.
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.
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.
Factors to monitor that affect SQL Server 2000 performance include hardware resources, network traffic, database design, and client applications.
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.
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.