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, cache hit ratios, disk I/O, and network traffic.
The SQL Server Instance Box
The left column of the SQL Server Instance box contains information about the server
itself:
- Instance Name: Displays the name of the monitored SQL
Server instance.
- SQL Server Version: Displays instance version and SP. Move your mouse over the
displayed value to get detailed
information about the server such as minor version and edition.
- Running Since: Displays the time when the instance was last
started.
- Server Time: Displays the current time on the server.
- Physical Memory: Displays the maximum amount of physical
memory available on the server.
The right column of the SQL Server Instance box contains information
about current and historical performance counters:
- Memory Used: Displays the amount of memory consumed by SQL
Server. 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.
- Disk Reads/sec: Displays the number of physical
(non-cached) 8-KB pages
read from disk per second.
- Disk Writes/sec: Displays the number of physical
(non-cached) 8-KB pages
written to disk per second.
- Network Sent/sec: Displays the amount of
network packets sent from SQL Server per second.
- Network Received/sec: Displays the amount of
network packets received by SQL Server per second.
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. If Disk Reads/sec and Disk Writes/sec are consistently high
(compared to disk capacity specified by manufacturer), consider moving some database files to an additional
disk or tuning your application for lower disk access (Consider index coverage, better
indexes, and/or normalization). You can also use a faster disk drive
or a RAID (Redundant Array of Inexpensive Disks) device to enhance
performance of I/O-bound applications.
The Chart Box
The Chart box allows you to view current and historical performance
counters in line charts (trends):
- CPU: Use the CPU chart to determine if CPU usage rates are within normal
ranges. This counter measures the amount of time the CPU is busy running
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. 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.
Note: CPUs with hyper-threading technology may report higher
percentages. If your system processors use hyper-threading, higher
percentages may not indicate over utilization. Additionally, Empty CPU
sockets (For example, a server with 4 sockets but only 2 CPUs) may
report lower CPU percentages. If your system has fewer CPUs than CPU
sockets, CPU percentages may not be accurate.
- Memory: Use the memory chart to determine if memory usage rates are
within normal ranges. The percentage indicated in the chart is the ratio
of used memory over physical memory (See Memory Used and Physical
Memory in the SQL Server Instance box).
- Cache Hit Ratio: Displays the buffer cache hit ration.
Buffer cache is the memory pool used to store SQL Server data. The
buffer cache hit ratio indicates the percentage of data
pages read from the buffer cache vs. disk. 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 cached disk I/O is crucial when monitoring the performance
of SQL Server. A value of 90% cache hit ratio indicates pages were
retrieved from memory 90% of the time. The other 10% were read from disk.
A consistent value below 90% indicates that more physical memory is
needed on the server.
- Proc Cache Hit Ratio: Displays the procedure cache hit
ratio. Procedure cache is the memory pool used to store SQL Server
execution plans. The procedure cache hit ratio indicates the
percentage of execution plan pages read from the procedure cache vs.
disk.
|
