System Utilization
The System Utilization pane is displayed when you navigate to a
server node in Server Explorer. It shows information about the most
important aspects of performance monitoring, including CPU usage, memory
usage, disk I/O, network traffic, and user information.
The chart at the bottom of the System Utilization pane consists of three performance counters
(trends in line charts) to provide information about CPU, memory, and
disk I/O. Each counter is displayed in a different color.
Teratrax Performance Monitor has two counters to monitor disk I/O:
The I/O chart and the Disk Reads/Writes (Today) value in the Activity
box. Even though CPU capacity and available memory are crucial, disk I/O
is frequently the cause of bottlenecks in most systems. Paying extra
attention to disk I/O is worthwhile.
Performance counters in Teratrax Performance Monitor are useful for
real-time monitoring of SQL Server. They help you monitor events as they occur
and allow you to investigate and solve issues relating to slow and
inefficient
applications by continually monitoring your server
to find all intermittent performance problems.
The SQL Server Instance Box
The SQL Server Instance box contains information about the server
itself. The box displays server name, server time, server start date
(running since),
physical and used memory, and SQL Server version with SP and edition (Move your mouse over the
SQL Server Version value to get detailed
information about the server edition).
The Activity Box
The Activity box contains information about current user connections
and open transactions. It also displays information on total login attempts, disk reads/writes, network traffic, and
SQL Server log entries that happened today.
Network Packets (Today) allows you to monitor the amount of
traffic (Packets) Microsoft SQL Server is generating and determine if network usage rates are within normal ranges. A surge in network traffic
that correlates with a slow batch may indicate that the batch is
retrieving a large amount of data.
Login Attempts (Today) shows the number of both successful and unsuccessful
attempts to connect to the server. Connections to the server can be
originated from a person logging in to SQL Server or an
application/process establishing a connection. Certain applications
establish a large number of connections when working with databases. SQL
Agent for example may establish multiple connections every time a job is
fired on a schedule. If the frequency of the job is high, Login Attempts
may report a high number. If you experience a suspicious sudden surge in
Login Attempts, consider the possibility of a security attack.
CPU Chart
The CPU chart allows you to monitor
Microsoft SQL Server to determine if CPU usage rates are within normal
ranges. This counter measures the amount of time the CPU is busy running
non-idle SQL Server threads. A continually high CPU usage rate may
indicate the need for a CPU upgrade or the addition of multiple
processors. Alternatively, a high CPU usage rate may indicate a poorly
tuned application or SQL code. Optimizing the application can lower CPU
utilization. A consistent state of 80 to 90 percent may indicate the
need for a CPU upgrade or the addition of more processors.
When you examine processor usage, consider the type of work the
instance of SQL Server is performing. If SQL Server is performing a lot
of calculations, such as queries involving aggregates or memory-bound
queries that require no disk I/O, 100 percent of the processor's time
may not seem unusual. If this causes the performance of other applications to
suffer, try changing the workload.
CPU and I/O
Different disk controllers and drivers use different amounts of CPU
time to perform disk I/O. Efficient controllers and drivers use less
time, leaving more processing time available for user applications and
increasing overall throughput.
Memory Chart
The memory chart in the System Utilization pane allows you to monitor
the amount of memory used by Microsoft SQL Server to determine if memory
usage rates are within normal ranges. By default, SQL Server changes its
memory requirements dynamically based on available system resources. If
SQL Server needs more memory, it queries the operating system to
determine whether free physical memory is available and then uses the
memory available. If SQL Server does not need the memory currently
allocated to it, it releases the memory to the operating system.
However, the option to dynamically use memory can be overridden using
the min server memory, max server memory, and working
set size server configuration options.
I/O Chart
Since disk I/O is frequently the cause of bottlenecks in most
systems, Teratrax Performance Monitor has two performance counters for disk
I/O in the System Utilization pane: The I/O chart and the Disk
Reads/Writes (Today) value in the Activity box.
The I/O chart shows you the percentage of the I/O subsystem
time currently used by SQL Server. The I/O subsystem includes the system
bus, disk controller cards, disks, and many
other I/O devices. This counter allows you to determine if the I/O
subsystem usage rates are within normal ranges.
Disk Reads/Writes (Today) displays the number of disk reads
and writes that happened today. Unlike the value specified for each SQL
batch or stored procedure in the Slow SQL node, this value reports only
non-cached reads and writes.
If these two counter values are consistently high, consider:
- Move some database files to an additional
disk or server.
- Use a faster disk drive or a redundant
array of inexpensive disks (RAID) device.
- Add additional disks to a RAID array, if
one already is being used.
- Tune your application or database to
reduce disk access operations. Consider index coverage, better
indexes, and/or normalization.
Microsoft SQL Server uses Microsoft Windows I/O calls to perform disk
reads and writes. SQL Server manages when and how disk I/O is performed,
but the Windows operating system performs the underlying I/O operations.
Applications and systems that are I/O-bound may keep the disk constantly
active.
Memory and Disk I/O
The more memory your server has the more cached disk reads and writes
it can perform. Lack of system memory can cause high non-cached disk
reads and writes. Adding memory to your server can sometimes help lower
physical disk access.
|
