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

connection – displays information on each connection. connection uses the following monitoring tables:
- monProcessSQLText
- monProcessActivity
statement – displays information on each statement. statement uses the following monitoring tables:
- monProcessSQLText
- monProcessStatement
cpu | diskio | elapsed time – these parameters order the output of sp_monitor connection or sp_monitor statement.
- cpu – indicates the amount of CPU time consumed by each different connection or statement.
- diskio – indicates the number of physical reads performed by each connection or statement.
- elapsed time – indicates the sum of the CPU time and the wait times for each connection or statement.
event – displays three possibilities. When you specify:
- No option – only user tasks are displayed.
- sp_monitor, event, "-1" – wait information about all tasks, both user and system, is displayed.
- sp_monitor, event, "spid" – wait information pertaining to only the specified server process ID is displayed.
spid – allows you to obtain event information for a specific task by entering its spid. You must specify the numeric value of spid within quotation marks.
procedure – displays statistics about stored procedures:
- ProcName – the stored procedure being monitored.
- DBNAME – the database in which the stored procedure is located.
- NumExecs – the approximate number of executions of this specific stored procedure.
- AvgCPUTime – the average CPU time that it takes for the stored procedure to execute.
- AvgPhysicalReads – the average number of disk reads performed by the stored procedure.
- AvgLogicalReads – the average number of logical reads performed by the stored procedure.
- AvgMemUsed_KB – the average amount of memory in KB used by the stored procedure.
procedure uses the monSysStatement monitoring table.
dbname – displays information on procedures for the specified database.
procname – displays information on the specified procedure.
summary | detail – displays either summary information, which provides an average of all instances of the procedure, or detailed information, which provides information on every instance of the stored procedure.
enable – enables the new options for sp_monitor. It turns on the configuration parameter required to begin monitoring.
disable – disables monitoring.
help – displays the syntax and examples for sp_monitor, and also reports extensive information on using this procedure for deadlock analysis:
```
sp_monitor 'help', 'deadlock'
```
The help option also provides command-specific examples.
deadlock – tells sp_monitor to process historical data from the monDeadlock table, and prints out a block of output for each instance of deadlock.
procstack – examines the execution context of a task, including that of a deeply nexted stored procedure. The stack of procedures executed is extracted from the monProcessProcedures monitoring table.

Examples

Example 1 – Reports information about how busy the SAP ASE server has been:

sp_monitor

last_run               current_run          seconds 
-------------------    -------------------  --------- 
Jan 29 1987 10:11AM    Jan 29 1987 10:17AM  314

cpu_busy           io_busy     idle 
---------------    ---------   -------------- 
4250(215)-68%      67(1)-0%    109(100)-31% 

packets_received       packets_sent    packet_errors 
----------------       ------------    ------------ 
781(15)                10110(9596)     0(0) 

total_read     total_write total_errors    connections 
-----------    -------------------------   ----------- 
394(67)        5392(53)    0(0)            15(1)

Example 2 – Shows how to display information about connections:

1> sp_monitor "connection"
2> go
spid    LoginName    ElapsedTime  LocksHeld  SQLText  
----    ---------    -----------  ---------  -----------------
12      sa           90300        2          exec get_employee_salaries
27      sa           17700        1          exec get_employee_perks

By default, the output by default is sorted in the descending order of the ElapsedTime.

Example 3 – Identifies the connections performing the most physical reads:

1> sp_monitor "connection","diskio"
2> go
spid  LoginName  Physical_Reads  LocksHeld  SQLText 
----  ---------  --------------  ---------  -------------------------- 
12    sa         117             2          exec get_employee_salaries 
27    sa         1               0          exec get_employee_perks

Example 4 – Displays information about each statement:

1> sp_monitor "statement"
2> go
spid   LoginName   ElapsedTime   SQLText
----   ---------   -----------   -------------------------- 
12     sa          100           exec get_employee_salaries

Example 5 – Displays the events each task spent time waiting for and the duration of the wait, reported in descending order of wait times:

1> sp_monitor "event"
2> go
SPID   WaitTime    Description 
------ ----------- ------------------------------------------ 
6      108200      hk: pause for some time 
29     108200      waiting for incoming network data 
10     107800      waiting while allocating new client socket 
15     17100       waiting for network send to complete 
14     5900        waiting for CTLIB event to complete 
14     400         waiting for disk write to complete 
7      200         hk: pause for some time 
7      100         waiting on run queue after yield 
12     100         waiting for network send to complete

Example 6 – Displays event data for spid 14:

1> sp_monitor "event","14"
2> go
WaitTime    Description 
----------- ----------------------------------- 
9000 waiting for CTLIB event to complete 
600 waiting for disk write to complete 
200 waiting for disk write to complete 
100 waiting on run queue after yield 
100 wait for buffer write to complete

Example 7 – Provides a summary of most recently run procedures, sorted in descending order of average elapsed time. This example provides historical monitoring information rather than the current state.

1> sp_monitor "procedure"
2> go

Average Procedure Statistics
============================

ProcName       DBName    AvgElapsedTime   AvgCPUTime  AvgWaitTime AvgPhysicalReads AvgLogicalReads AvgPacketsSent NumExecs 
-----------------------------------------------------------------------neworder_remote   tpcc   1833     16    1083     26   96    0     6
neworder_local    tpcc   1394     13    1181     31   122   0     38
tc_startup        tpcc   1220     3     1157     0    3     0     59
delivery          tpcc   1000     0     800      23   49    0     2

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 specifysp_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:

Information	Values
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