6 Monitoring SQL Server Performance Objectives Monitoring Resource
6. Monitoring SQL Server Performance • Objectives – Monitoring Resource Usage • Contents – Developing a Performance Monitoring Methodology – Choosing Among Monitoring Tools – Performing Monitoring Tasks • Practicals – Monitor your SQL server system • Summary
Developing a Performance Monitoring Methodology • Establish appropriate and effective monitoring goals • Determine which activities and resources to monitor • Develop a long-term monitoring strategy • Establishing Monitoring Goals • Before you begin monitoring SQL Server 2000 performance, you should determine your monitoring goals • User perspective – To minimize the response time for each query submitted by each user • Server perspective – To maximize the total server throughput of queries submitted by users
Identifying Performance Bottlenecks • Inadequate hardware resources, such as memory or processor, are common causes of bottlenecks. • Low gauge meter numbers can mean that the system is performing better than expected, but they can also reveal a performance bottleneck. Usally beyond the server hardware. Can be network or software bottlenecks. • Some bottlenecks can be solved by adding additional hardware resources or by moving some of the load to other servers. • Solving one performance bottleneck can reveal another performance bottleneck. • 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. • 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
Determining Resources and Activities to Monitor • Hardware resources might be inadequate for the load on the server • Competing server applications on the SQL Server 2000 computer might be using excessive resources • Hardware resource use might be unbalanced • A hardware resource might be malfunctioning • General network congestion might occur • Improper use of cursors or ad hoc queries • Poor database design • Poorly written applications
Choosing Among Monitoring Tools • Using System Monitor – Windows 2000 System Monitor is used to monitor resource usage on either the local computer or a remote computer • Using Task Manager – Task Manager is used to provide a snapshot in real time • Using SQL Profiler – SQL Profiler is a graphical SQL Server 2000 tool used to monitor (trace) selected SQL Server events • Using the SQL Server Enterprise Manager Current Activity Window – The SQL Server Enterprise Manager Current Activity window displays a snapshot of information regarding processes, user activity, locks held by processes, and locks held on objects • Using SQL Query Analyzer – The system stored procedures you can use to monitor SQL Server 2000 activity and performance • Using SNMP – SNMP can only monitor the default instance. – SQL Server 2000 support for SNMP is enabled automatically
Performing Monitoring Tasks • Monitoring Resource Usage – memory, I/O, and processor (task manager for momentary values) • System Monitor To monitor resource use with System Monitor, click Performance in the Administrative Tools program group. 1. Memory Objects and Counters (Table 14 -7: Memory Object Counters ) 2. I/O Objects and Counters (Table 14 -8: . I/O Object Counters) 3. Processor Objects and Counters (Table 14 -9: . Processor Object Counters )
Performing Monitoring Tasks • Monitoring Resource Usage – memory, I/O, and processor (task manager for momentary values) • System Monitor To monitor resource use with System Monitor, click Performance in the Administrative Tools program group. 1. Memory Objects and Counters (Table 14 -7: Memory Object Counters ) 2. I/O Objects and Counters (Table 14 -8: . I/O Object Counters) 3. Processor Objects and Counters (Table 14 -9: . Processor Object Counters )
- Slides: 8
