sp_sysmon

Description

Displays performance information.

Syntax

sp_sysmon begin_sample

sp_sysmon { end_sample | interval } [, section [, applmon] ]

sp_sysmon { end_sample | interval } [, applmon ]

Parameters

begin_sample: starts sampling. You cannot specify a section when you specify begin_sample.

end_sample: ends sampling and prints the report.

interval: specifies the time period for the sample. It must be in HH:MM:SS form, for example “00:20:00”.

section

is the abbreviation for one of the sections printed by sp_sysmon. Table 1-20 lists the values and corresponding names of the report sections.

**Table 1-20: sp_sysmon report sections**
Report section	Parameter
Application Management	appmgmt
Data Cache Management	dcache
Disk I/O Management	diskio
ESP Management	esp
Index Management	indexmgmt
Kernel Utilization	kernel
Lock Management	locks
Memory Management	memory
Metadata Cache Management	mdcache
Monitor Access to Executing SQL	monaccess
Network I/O Management	netio
Parallel Query Management	parallel
Procedure Cache Management	pcache
Recovery Management	recovery
Task Management	taskmgmt
Transaction Management	xactmgmt
Transaction Profile	xactsum
Worker Process Management	wpm

applmon

specifies whether to print application detail, application and login detail, or no application detail. The default is to omit the application detail. Valid values are listed in Table 1-21.

Table 1-21: Values for applmon parameter to **sp_sysmon**
Parameter	Information reported
appl_only	CPU, I/O, priority changes and resource limit violations by application name.
appl_and_login	CPU, I/O, priority changes and resource limit violations by application name and login name.
no_appl	Skips the by application or by login section of the report. This is the default.

This parameter is only valid when printing the full report and when you specify appmgmt for the section.

Examples

Example 1

Prints monitor information after 10 minutes:

sp_sysmon "00:10:00"

Example 2

Prints only the “Disk Management” section of the sp_sysmon report after 5 minutes:

sp_sysmon "00:05:00", diskio

Example 3

Starts the sample, executes procedures and a query, ends the sample, and prints only the “Data Cache” section of the report:

sp_sysmon begin_sample
go
execute proc1
go
execute proc2
go
select sum(total_sales) from titles
go
sp_sysmon end_sample, dcache
go

Example 4

Prints the full report and includes application and login detail for each login:

sp_sysmon "00:05:00", @applmon = appl_and_login

Usage

sp_sysmon displays information about Adaptive Server performance. It sets internal counters to 0, then waits for the specified interval while activity on the server causes the counters to be incremented. When the interval ends, sp_sysmon prints information from the values in the counters. See the Performance and Tuning Guide for more information.
To print only a single section of the report, use the values listed in Table 1-21 for the second parameter.
If you use sp_sysmon in batch mode, with begin_sample and end_sample, the time interval between executions must be at least one second. You can use waitfor delay "00:00:01" to lengthen the execution time of a batch.
During the sample interval, results are stored in signed integer values. Especially on systems with many CPUs and high activity, these counters can overflow. If you see negative results in your sp_sysmon output, reduce your sample time.

Permissions

Only a System Administrator can execute sp_sysmon.