Transcript Unix System Admin

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 )