Using The System Monitor Tool For Measuring SQL Server Performance
a program to measure the performance of SQL Server allows you to view the
objects of SQL Server and performance counters in addition to the behavior of
other objects, including threads, processors, memory, and cache. These objects
individually associate set of computers for measuring delays, device usage, and
other counters indicating overall performance status. You might have heard
about the program before as a monitoring tool for Windows OS; however, it has
the ability to monitor SQL Server and Windows OS at the same time to find out
the relation between the both. This is why the tool is also well known as SQL
Server Performance Monitor. In order to determine the behavior of the
system, the program will monitor the SQL Server Buffer Manager counters and
disk I/O counters. Moreover, you can obtain SQL Server performance and activity
statistics. The System Monitor is elaborated in the following sections:
How System Monitor proves to be
Monitor tool benefits you in the following ways:
- You can view
the data from multiple computers at the same time.
- You can view
the charts that show current activity and change them. These charts also display
the updated counter values at user-defined frequency.
- You can add
system alerts that provide the details of event in alert log. The added
alerts issue a network alert to notify you.
- It allows you
to export data from alert logs, charts and reports to a database so that
it can be re-manipulated.
- The tool lets
you create log files that comprise objects’ data from different systems.
- You can view
activity reports or create new reports with the help of current log files.
- You are
allowed to run a predefined application when counter value has a mismatch
with user-defined value.
You can save the chart, log, alert, report
You can select sections from existing files and
append them to a single file to create archive.
Areas to Assess
you attempt to monitor MS Windows OS or SQL Server for performance, the
following three areas are the primary focus: processor utilization, disk
activity, and memory usage.
If you monitor
a computer where System Monitor is in process, you may experience some
performance decline. Therefore, it is recommended to either log the data of
System Monitor to some other disk or run the program from a computer placed at
a remote location. Moreover, it is better to monitor some specific counters
that you want because monitoring a large number of counters leads to excessive
resource usage that results in sluggish performance of the system you are
Tasks of System Monitor
Running System Monitor
Now is the time
to know how the SQL Server Performance Monitor program manages to
monitor SQL Server. It is all done with the participation of RPCs (remote
procedure calls) used by System Monitor for collecting information from SQL
Server. If you have Windows rights to use the program, you are also permitted
to use it for monitoring MS SQL Server. When you monitor SQL Server using
System Monitor, a little performance overhead is sure that depends on number of
counters, hardware platform, and the update interval that has been selected. It
is equally important to know that the System Monitor tool is integrated with
SQL Server to handle performance if it degrades.
Monitoring Disk Usage
performs read/write operations on the disk with the help of Windows
input/output calls and keeps track of the way I/O is performed. The
input/output subsystem includes various components, including disks, system
bus, CD-ROM drive, controller cards, and other I/O devices. The System Monitor
tool mainly targets the following areas while it monitors disk activity:
It monitors disk I/O and detects excess paging.
It isolates the disk activity created by SQL
Monitoring CPU Usage
is also responsible for assessing CPU if usage rates fall in the normal range.
If it is found that CPU usage rate is higher than the prescribed range, the
application design is poor and the situation is also an indication that CPU
needs to be upgraded or multiple processors are required. Poor application
design problem can be resolved by optimizing it, which results in relatively
lower CPU usage. On an average, when CPU usage reaches 80-90%, the need to
upgrade the CPU or to add multiple processors falls.
Utilization Can be Determined?
A simple way
for measuring the CPU Utilization is using the counter ‘Processor: % Processor Time’
in System Monitor. With the
counter, it is easy to monitor the time taken by CPU to execute an active
thread. A system that contains multiple processors, each of which has to be monitored;
the separate instance of the counter for every single processor should be
monitored. On the other hand, the counter ‘System: %Total Processor Time’ is
used if average for all the system processors is to be determined instead of
the following counters also contribute to monitoring CPU usage:
Processor: % Privileged Time: The counter monitors
the time taken by the processor for executing SQL Server I/O requests and other
Windows kernel commands.
Processor: %User Time: It monitors the time consumed
by the processor for executing SQL Server and other user processes.
System: Processor Queue Length: This counter
corresponds to the count of threads that wait for the processor time. When
threads within a process need more than available processor cycles, processor
bottleneck situation occurs. Furthermore, if a good number of processes try to
use processor’s time, installation of a faster processor may be needed. In case
of a system having multiple processors, a processor can be added.
In order to
determine the processor usage, you need to consider the type of work performed
by an instance of SQL Server.
Monitoring Memory Usage
Memory usage is a major concern that is monitored by
System Monitor. The tool keeps a check that memory usage is limited to its
range by monitoring the instance of SQL Server at certain intervals. The below
given object counters monitor low-memory condition:
As the name indicates, the counter provides the number of available bytes that
can be used by processes. If Available Bytes displays low values, it is a clear
indication of memory shortage on the computer.
counter is responsible to indicate the following page count:
extracted from disk because of hard page faults
- Pages written
to the free space on the disk because of page faults
The high rating
of Pages/sec counter is an indication of excessive paging. Monitoring the ‘Memory: Page Faults/sec’ counter
helps to determine that paging is not the cause of disk activity. Moreover, if
‘Process: Page Faults/sec‘
counter is monitored for an instance of SQL Server process, it is helpful to
determine whether the SQL Server or any other process causes the excessive
note includes that SQL Server has the ability and flexibility to change memory
requirements according to the available memory resources. In case of
requirement of additional memory, it tells the Windows OS to figure out the
availability of physical memory. If the memory is available, SQL Server uses
it. On the other side, if SQL Server has additional memory that does not have
to be used by it, this memory is simply released to the OS. If required, the
server configuration options ‘max server memory’ and ‘min server
memory’ can be used to override the described option.
You need to
check the below given performance counters for monitoring the amount of memory
used by the SQL Server:
SQL Server: Memory Manager: Total Server
SQL Server: Buffer Manager: Buffer Cache
· Process: Working Set
SQL Server: Buffer Manager: Total Pages
Creating a SQL Server Database Alert
has the ability to allow the creation of an alert that becomes active when
System Monitor counter value is reached. System Monitor responds to the alert
by launching an application. You can define an alert using SQL Server Agent and
SQL Server Management Studio.
the requirements for defining an alert:
- Name of the
- An event or
performance condition to trigger the alert
- An action
taken by SQL Server Agent to respond to the specified event or performance
Creating Alerts, Reports,
Charts and Logs
also allows to create Alerts, Reports, Charts and Logs for monitoring an
instance of SQL Server. Charts, reports and alerts for an activity provide feedback
instantly and log files allow you to keep track of counters for a long time
period. Below section provides a descriptive look at the tasks handled by each
Alerts: As explained above, alerts are raised to notify you when some
specific events occur. A log for an alert is able to monitor performance of
object instances and counters that you have selected. A notification is
followed by recording of event date and time by the alert log when the given
value is exceeded by the counter. Moreover, you can also create a network alert
generated by an event.
Reports: Reports are used to show the values that change continuously.
These values are for selected objects and are displayed in columns.
Charts: You can create charts for monitoring the performance of desired
objects and counters. You can add the following to a chart:
Combinations of objects and counters of System
Objects and counters of MS Windows
charts is advantageous in the following ways:
To determine the cause of low performance of a
computer or application
To identify the reason for the need of capacity
To display a comparison in the form of histogram
To monitor the system regularly to find
To display a trend as line chart
To monitor a local or remote computer on a
real-time basis for short term
Each of the
charts you have created displays a part of information to be monitored.
Logs: You can have a record of current activity of the objects and
computers that you have selected by creating logs that are used later to view
them and do further analysis. A single log file is capable to save data from
multiple computers. For instance, to collect performance information about the
selected objects on different computers, different logs can be created. You are
also enabled to save the selections and use them later. The entire log
information is sufficient to troubleshoot the issues or do further planning.
Using SQL Server Objects
uses objects and counters for monitoring activity on the computer systems that
run an instance of SQL Server; these objects and counters are provided by SQL
Server as an object is a resource of SQL Server. Each object houses multiple
counters that are responsible to determine the aspects of objects that have to
be monitored. For example, the resource SQL Server Locks consists of two
· Number of Deadlocks/sec
· Lock Timeouts/sec
Note that some
objects may have multiple instances. It generally happens when multiple
resources of the specified type lie on a single computer, such as the object
‘Processor’ contains multiple instances in case of multiple processors on one
computer. On the other hand, some objects have a single instance, for example,
the object ‘Database’ has a single instance for every single database on MS SQL
Server. In case of multiple instances of an object, counters can be added for
tracking statistics of each instance. Moreover, you can display SQL Server
counter statistics by configuring the System Monitor tool. You are also enabled
to set a value for a counter of SQL Server and create an alert when the counter
reaches or exceeds this value.
However, it is
important for you to note that for displaying the SQL Server statistics,
installation of an instance is mandatory. Additionally, if a SQL Server
instance is stopped or even restarted, the statistics display process will be
interrupted in between. A great thing is you are able to view the SQL Server
counters in snap-in of System Monitor even when SQL Server is not in running