sp_sysmon

Displays performance information.

Syntax

sp_sysmon {'begin_sample' | {{'end_sample' | 'interval'}[, section]}}
	[, applmon=
		{'appl_only' | 'appl_and_login' | 'no_appl' | 'noclear' | 'clear'} 
	[, filter={cache_wizard_filter_value | 'noclear' | 'clear'} 
	[, dumpcounters={'y' | 'n' | 'noclear' | 'clear' | NULL} 
	[, option={'noclear' | 'clear' | NULL}]]]]

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. The values and corresponding names of the report sections are:
- appmgmt – Application Management
- cache wizard – Cache Wizard
- dcache – Data Cache Management
- diskio – Disk I/O Management
- esp – ESP Management
- housekeeper – Housekeeper Task Activity
- indexmgmt – Index Management
- kernel – Kernel Utilization
- locks – Lock Management
- memory – Memory Management
- mdcache – Metadata Cache Management
- monaccess – Monitor Access to Executing SQL
- netio – Network I/O Management
- parallel – Parallel Query Management
- pcache – Procedure Cache Management
- recovery – Recovery Management
- repagent – RepAgent
- taskmgmt – Task Management
- xactmgmt – Transaction Management
- xactsum – Transaction Profile
- wpm – Worker Process Management
You can also obtain most of the information available through sp_sysmon mdcache report using sp_monitorconfig.
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 and the information they report are:
- 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.
noclear | clear – specifies whether to clear or not clear monitor counters:
- clear – explicity clears the monitor counters.
- noclear – sp_sysmon does not clear the monitor counters. The primarily purpose of the noclear parameter is to provide backward compatibility (earlier versions of sp_sysmon cleared monitor counters by default).
Note: You can use the noclear parameter only when you specify a sample interval in sp_sysmon. You cannot use noclear if you specify begin_sample or end_sample.

By default, sp_sysmon does not clear the monitor counters that are used as source data for the report. If other applications or instances of the sp_sysmon report are running when this is done, clearing the counters may cause the data that they report to be invalid.
filter – is a varchar datatype that allows you to specify a pattern for the cache(s) included in the report.
For example, if it is specified as default data cache, the report only contains information about the default data cache. If it is specified as emp%, the output includes information on all caches with a name matching this pattern.If no value is given the output contains all the caches with the default data cache appearing first, followed by the other caches in alphabetical order.
'cache wizard' – aids in the monitoring and configuring of data caches for optimal performance.
dumpcounters –
returns the contents of the master..sysmonitors table (which contains the names and values of all monitor counters) as a result set, after returning the requested report sections.
option – allow you to specify the clear or noclear parameters if you used all the other sp_sysmon parameters to specify alternative sp_sysmon behaviors. Valid values are clear and noclear.

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
```

Example 6 – Prints a report using the cache wizard:

sp_sysmon '00:00:30', 'cache wizard'

=============================================================================
Cache Wizard
=============================================================================

------------------
default data cache
------------------Run Size        :   100.00 Mb   Usage%              :      2.86
LR/sec          :    41.10      PR/sec              :     22.57   Hit%:  45.09
Cache Partitions:     4         Spinlock Contention%:      0.00
Buffer Pool Information
-----------------------------------------------------------------------------


IO Size Wash Size  Run Size    APF%   LR/sec   PR/sec   Hit%   APF-Eff% Usage%
------- ---------- ----------- ------ -------- -------- ------ -------- ------
4 Kb     3276 Kb    16.00 Mb  10.00     0.47     0.13  71.43    n/a     0.20
2 Kb    17200 Kb    84.00 Mb  10.00    40.63    22.43  44.79    n/a     3.37

(1 row affected)

Object Statistics 

-----------------------------------------------------------------------------
Object                          LR/sec  PR/sec  Hit%   Obj_Cached% Cache_Occp%
------------------------------- ------- ------- ------ ----------- -----------
empdb.dbo.t1                      0.57    0.30  47.06       56.25        0.02
empdb.dbo.t2                      0.30    0.30   0.00       56.25        0.02
empdb.dbo.t3                      0.30    0.30   0.00       56.25        0.02
empdb.dbo.t4                      0.30    0.30   0.00       56.25        0.02
empdb.dbo.t5                      0.30    0.30   0.00       56.25        0.02
empdb.dbo.t6                      0.30    0.30   0.00       56.25        0.02
empdb.dbo.t8                      0.30    0.30   0.00       56.25        0.02
empdb.dbo.t7                      0.57    0.20  64.71       62.50        0.02
tempdb.dbo.tempcachedobjstats     3.63    0.00 100.00       50.00        0.01
tempdb.dbo.tempobjstats           0.47    0.00 100.00       25.00        0.00

Object                          Obj Size    Size in Cache
------------------------------- ----------- -------------
empdb.dbo.t1                         32 Kb       18 Kb
empdb.dbo.t2                         32 Kb       18 Kb
empdb.dbo.t3                         32 Kb       18 Kb
empdb.dbo.t4                         32 Kb       18 Kb
empdb.dbo.t5                         32 Kb       18 Kb
empdb.dbo.t6                         32 Kb       18 Kb
empdb.dbo.t8                         32 Kb       18 Kb
empdb.dbo.t7                         32 Kb       20 Kb
tempdb.dbo.tempcachedobjstats        16 Kb        8 Kb
tempdb.dbo.tempobjstats              16 Kb        4 Kb

---------
company_cache
--------- 
Run Size        :     1.00 Mb   Usage%              :      0.39
LR/sec          :     0.07      PR/sec              :      0.07   Hit%:   0.00
Cache Partitions:        1      Spinlock Contention%:      0.00

Buffer Pool Information
-----------------------------------------------------------------------------
IO Size Wash Size  Run Size    APF%   LR/sec   PR/sec   Hit%  APF-Eff% Usage%
------- ---------- ----------- ------ -------- -------- ------ -------- ------
2 Kb      204 Kb      1.00 Mb  10.00    0.07    0.07    0.00    n/a     0.39
Object Statistics
-----------------------------------------------------------------------------

Object               LR/sec  PR/sec  Hit%   Obj_Cached% Cache_Occp%
-------------------- ------- ------- ------ ----------- -----------
empdb.dbo.history     0.07    0.07   0.00      25.00      0.39

Object               Obj Size    Size in Cache
-------------------- ----------- -------------
empdb.dbo.history      16 Kb      4 Kb

-------------
companydb_cache
-------------
Run Size        :     5.00 Mb   Usage%              :    100.00
LR/sec          :   380.97      PR/sec              :     56.67   Hit%:  85.13
Cache Partitions:        1      Spinlock Contention%:      0.00

Buffer Pool Information
-----------------------------------------------------------------------------
IO Size Wash Size  Run Size    APF%   LR/sec   PR/sec   Hit%   APF-Eff% Usage%
------- ---------- ----------- ------ -------- -------- ------ -------- ------
2 Kb     1024 Kb    5.00 Mb  10.00    380.97    56.67  85.13  98.42   100.00 


Object Statistics
-----------------------------------------------------------------------------

Object                         LR/sec  PR/sec  Hit%   Obj_Cached% Cache_Occp%
----------------------------- ------- ------- ------ ----------- -----------
company_db.dbo.emp_projects     41.07   22.80  44.48       19.64        9.45
company_db.dbo.dept_det         93.03   20.67  77.79       99.08       54.53
company_db.dbo.emp_perf        116.70    2.63  97.74       97.77       34.18
company_db.dbo.dept_locs         0.43    0.17  61.54       50.00        0.16

Object                          Obj Size    Size in Cache
----------------------------- ----------- -------------
company_db.dbo.emp_projects       2464 Kb      484 Kb
company_db.dbo.dept_det           2818 Kb     2792 Kb
company_db.dbo.emp_perf           1790 Kb     1750 Kb
company_db.dbo.dept_locs            16 Kb        8 Kb

TUNING RECOMMENDATIONS
--------------------------------------------------------------------
Usage% for 'default data cache' is low (< 5%)
Usage% for 4k buffer pool in cache:default data cache is low (< 5%)
Usage% for 2k buffer pool in cache:default data cache is low (< 5%)

Usage% for 'company_cache' is low (< 5%)
Usage% for 2k buffer pool in cache:company_cache is low (< 5%)
Consider adding a large I/O pool for 'companydb_cache'

Usage

There are additional considerations when using sp_sysmon:

sp_sysmon displays information about SAP ASE 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 valid values for sp_sysmon applmon.
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.

See also Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon.

Permissions

You must be a user with execute permission to run sp_sysmon. The permission can be granted to other users by the database owner of sybsystemprocs.

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