sp_monitor

Displays statistics about the SAP ASE server.

Syntax

sp_monitor syntax is divided by command type for clarity, since many of the types have parameters of their own. The following code paragraph shows the syntax of the stored procedure as a whole, followed by the syntax of each command type interface.
sp_monitor [[connection | statement], [cpu | diskio | elapsed time]]  
	[event, [spid ]]
	[procedure, [dbname, [procname[, summary | detail]]]]
	[enable] [disable] 
	[help], 
	[deadlock][procstack]

Parameters

Examples

Usage

Note: Before using the new parameters associated with sp_monitor, you must set up monitoring tables and the related stored procedures needed to enable. See Installing Monitoring Tables in Performance and Tuning: Monitoring and Analyzing.
  • The SAP ASE server keeps track of how much work it has done in a series of global variables. sp_monitor displays the current values of these global variables and how much they have changed since the last time the procedure executed.

  • For each column, the statistic appears in the form number(number)-number% or number(number).
    • The first number refers to the number of seconds (for cpu_busy, io_busy, and idle) or the total number (for the other columns) since the SAP ASE server restarted.

    • The number in parentheses refers to the number of seconds or the total number since the last time sp_monitor was run. The percent sign indicates the percentage of time since sp_monitor was last run.

    For example, if the report shows cpu_busy as “4250(215)-68%”, it means that the CPU has been busy for 4250 seconds since the SAP ASE server was last started, 215 seconds since sp_monitor last ran, and 68 percent of the total time since sp_monitor was last run.

    For the total_read column, the value 394(67) means there have been 394 disk reads since the SAP ASE server was last started, 67 of them since the last time sp_monitor was run.

  • This list shows the monitoring tables accessed by monitoring type, as well as the configuration option and its type for each table:
    • connection
      • monProcessSQLext
        • max SQL text monitored – Value
        • SQL batch capture – Boolean
      • monProcessActivity
        • wait event timing – Boolean
        • per object statistics active – Boolean
    • procstack
      • monProcessProcedures
        • None – N/A
    • statement
      • monProcessSQLText
        • max SQL text monitored – Value
        • SQL batch capture – Boolean
      • monProcessStatement
        • statement statistics active – Boolean
        • per object statistics active – Boolean
        • wait event timing – Boolean
    • event
      • monProcessWaits
        • wait event timing – Value
        • process event waits – Boolean
    • procedure
      • monSysStatement
        • statement statistics active – Boolean
        • per object statistics active – Boolean
        • statement pipe max messages – Value
        • statement pipe active – Boolean
    • deadlock
      • monDeadlock
        • deadlock pipe max messages – Value
        • deadlock pipe active – Boolean
  • sp_monitor connection monitors connections actively executing T-SQL only, and does not report on all connections.

  • You must run sp_monitor from the master database. However, if you are analyzing deadlock data archived in another database, you can run sp_monitor deadlock from that database.

  • sp_monitor event no longer displays all tasks (including system tasks), when called with no options. In SAP ASE version 15.0.2 and above, the event option provides three possibilities. When:

    • No option is provided – only user tasks are displayed.

    • You specify sp_monitor, event, "-1", wait information about all tasks, both user and system, is displayed.

    • You specify sp_monitor, event, "spid", wait information pertaining to only the specified server process ID is displayed.

  • The following table describes the columns in the sp_monitor report, the equivalent global variables, if any, and their meanings. With the exception of last_run, current_run and seconds, these column headings are also the names of global variables—except that all global variables are preceded by @@. There is also a difference in the units of the numbers reported by the global variables—the numbers reported by the global variables are not milliseconds of CPU time, but machine ticks.

    Column Heading

    Equivalent Variable

    Description

    last_run

    Clock time at which the sp_monitor procedure last ran.

    current_run

    Current clock time.

    seconds

    Number of seconds since sp_monitor last ran.

    cpu_busy

    @@cpu_busy

    Number of seconds in CPU time that the SAP ASE server’s CPU was doing SAP ASE work.

    io_busy

    @@io_busy

    Number of seconds in CPU time that the SAP ASE server has spent doing input and output operations.

    idle

    @@idle

    Number of seconds in CPU time that the SAP ASE server has been idle.

    packets_received

    @@pack_received

    Number of input packets read by the SAP ASE server.

    packets_sent

    @@pack_sent

    Number of output packets written by the SAP ASE server.

    packet_errors

    @@packet_errors

    Number of errors detected by the SAP ASE server while reading and writing packets.

    total_read

    @@total_read

    Number of disk reads by the SAP ASE server.

    total_write

    @@total_write

    Number of disk writes by the SAP ASE server.

    total_errors

    @@total_errors

    Number of errors detected by the SAP ASE server while reading and writing.

    connections

    @@connections

    Number of logins or attempted logins to the SAP ASE server.

  • The first time sp_monitor runs after SAP ASE start-up, the number in parentheses is meaningless.

  • The SAP ASE server’s housekeeper task uses the server’s idle cycles to write changed pages from cache to disk. This process affects the values of the cpu_busy, io_busy, and idle columns reported by sp_monitor. To disable the housekeeper task and eliminate these effects, set the housekeeper free write percent configuration parameter to 0:
    sp_configure "housekeeper free write percent", 0
  • You must run sp_monitor when a representative workload is running on the system.

  • Typically, run procedures in this sequence:

    • Run sp_monitor enable

    • Invoke sp_monitor options

    • Run sp_monitor disable when you have completed the monitoring

  • When you are using sp_monitor procedure, the number of rows returned can be very large; you may want to use the summary option instead of the detail option. It may also take a while for this command to complete on an active system.

Permissions

The permission checks for sp_monitor are the same whether or not granular permissions is enabled:
  • The database owner of sybsystemprocs can execute sp_monitor and can grant execute permission to other users

  • The stored procedure is created with execute as owner. The owner is sa. The owner must have mon_role which user sa has by default.

For more information see Monitoring Tables in Performance and Tuning: Monitoring and Analyzing.

Auditing

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

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • 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

Related reference
sp_who