sp_sysmon provides a detailed report of the activity on your system, and provides you with a number of ways to specify the type of information you want to receive, the time interval during which data for the report will be collected, and other options that determine how the report is generated.
The sp_sysmon report consists of a number of separate sections. You can run sp_sysmon to generate the complete report or only one of the individual sections. You can also specify a time interval over which the report should run or execute the stored procedure yourself at the beginning and end of the desired time period.
sp_sysmon reports monitoring data only from the sample period. Make sure you make decisions about tuning based on representative data. For example, to tune spinlocks, base decisions on data from a peak utilization report. However, base a decision to decrease the number of engines on a number of samples representing typical and peak workloads.
The data for the report is collected from a set of monitor counters that are maintained by Adaptive Server®. These counters are also used by other applications. By default, the sp_sysmon report clears these counters when it is invoked. Clearing the counters can interfere with other applications using the counters, causing the data they report to be invalid.
WARNING! To control whether sp_sysmon clears
the counters, specify the noclear option. When
you specify the noclear option, sp_sysmon does
not clear the counters, which allows sp_sysmon to
run at the same time as other sp_sysmon sessions.
By default, noclear is enabled when you run sp_sysmon using
a sample interval, and disabled when you run sp_sysmon using
the begin_ and end_sample parameters.
See “Using the noclear option” for more
information.
sp_sysmon contributes
approximately 5 to 7% overhead while it runs on a single
CPU server, and more on multiprocessor servers (the percentages
may be different for your site). The amount of overhead increases
with the number of CPUs.sp_sysmon noclear uses the
same internal counters. When not run with the option, sp_sysmon resets
these counters to 0.
There are counters shared between sp_sysmon and the monitoring tables. Starting a second execution of sp_sysmon while an earlier execution is running can clear all the counters, so the reports generated by the first sp_sysmon session are inaccurate.
For information on running sp_sysmon with other applications see “Using the noclear option”.
The performance tuning tips for sp_sysmon are based on the sampling interval you supply with sp_sysmon. Review the recommendations thoroughly, based on your system requirements, before you incorporate them in your production system. Sybase® strongly recommends that you set up a test area with your data, and test any changes before you implement any of the recommendations.
Since sp_sysmon provides a snapshot view of the system, you may need to reconsider recommendations when the workload changes.
You cannot run sp_sysmon on
an Adaptive Server with a tempdb that is the
default size. Increase the size of tempdb by
at least 2MB so that Adaptive Server does not run out of log space
on the temporary databases.
