Displays statistics about Adaptive Server.
sp_monitor [ connection, [cpu | diskio | elapsed time ] ] [event, [spid ] ] [procedure, [ dbname, [ procname, [, summary | detail ] ] ] ] [ enable ] [ disable ] [ statement, [ cpu | diskio | elapsed time ] ] [ help], [ connection | statement | procedure | event ] ]
displays information on each connection. connection uses the following monitoring tables:
monProcessSQLText
monProcessActivity
these parameters order the output of sp_monitor connection. cpu indicates the amount of CPU time consumed by each different connection. diskio indicates the number of physical reads performed by each connection. elapsed time indicates the sum of the CPU time and the wait times for each connection.
displays information about the events each task spent time waiting for, and the duration of the wait. This is reported in descending order of wait time in milliseconds. event uses the following monitoring tables:
monProcessWaits
monWaitEventInfo
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.
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.
displays information on procedures for the specified database.
displays information on the specified procedure.
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.
enables the new options for sp_monitor. It turns on the configuration parameter required to begin monitoring.
disables monitoring.
sp_monitor statement displays information on each statement. statement uses the following monitoring tables:
monProcessSQLText
monProcessStatement
These parameters help order the output of sp_monitor statement. cpu indicates the the cpu time consumed by different statements. diskio indicates the number of physical reads done by different statements. elapsed time indicates the sum of the CPU time and the wait times for different statements.
displays the syntax and examples for sp_monitor.
Reports information about how busy Adaptive 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)
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.
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
Displays information about each statement:
1> sp_monitor "statement" 2> go spid LoginName ElapsedTime SQLText ---- --------- ----------- -------------------------- 12 sa 100 exec get_employee_salaries
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
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
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
Before using the new parameters associated with sp_monitor,
you must set up monitoring tables and the related stored procedures
needed to enable these options, which are part of the installmontables script.
For more information, see “Installing Monitoring Tables” in Performance
and Tuning: Monitoring and Analyzing.
Adaptive 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 Adaptive 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 Adaptive 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 Adaptive Server was last started, 67 of them since the last time sp_monitor was run.
Table 1-17 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.
The first time sp_monitor runs after Adaptive Server start-up, the number in parentheses is meaningless.
Adaptive 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, you will 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.
You must have mon_role permissions to execute sp_monitor. For more information see “Monitoring Tables” Performance and Tuning: Monitoring and Analyzing.
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 |
|
System procedures sp_who
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
