sp_syntax  sp_tempdb

Chapter 1: System Procedures

sp_sysmon

Description

Displays performance information.

Syntax

sp_sysmon begin_sample
sp_sysmon interval [, noclear,[,section  [, applmon]]]
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.

noclear

when this option is included, the monitor counters are not zeroed out.

section

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

Table 1-21: 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-22.

Table 1-22: 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.

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”.

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

Example 5

Report usage without clearing the counters:

sp_sysmon "00:01:00", kernel, noclear

You can also use:

sp_sysmon "00:01:00", noclear

NoteYou can use the noclear parameter only when you specify a sample interval in sp_sysmon. If you specify begin_sample or end_sample you cannot use noclear.

Usage

Permissions

Only a System Administrator can execute sp_sysmon.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect





Copyright © 2005. Sybase Inc. All rights reserved. sp_tempdb

View this book as PDF