Displays performance information.
sp_sysmon begin_sample
sp_sysmon { end_sample | interval }[, section[, applmon] ]
[, 'cache wizard' [, top_N [, filter] ] ]
starts sampling. You cannot specify a section when you specify begin_sample.
is the abbreviation for one of the sections printed by sp_sysmon. Table 1-40 lists the values and corresponding names of the 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 |
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-41.
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.
ends sampling and prints the report.
specifies the time period for the sample. It must be in HH:MM:SS form, for example “00:20:00”.
aids in the monitoring and configuring of data caches for optimal performance.
is a varchar datatype that limits the list of objects reported in the Object Section based on the ranking criteria for the number of logical reads in the specified interval (as displayed in the LR/sec column).
The order of ranking is ascending or descending based on whether the specified value is a positive or negative integer. The entire list of objects occupying the cache at the end of the interval can be obtained by specifying a value of “0.” The default value 10.
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 will only contain 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.
Prints monitor information after 10 minutes:
sp_sysmon "00:10:00"
Prints only the “Disk Management” section of the sp_sysmon report after 5 minutes:
sp_sysmon "00:05:00", diskio
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
Prints the full report and includes application and login detail for each login:
sp_sysmon "00:05:00", @applmon = appl_and_login
Report usage without clearing the counters:
sp_sysmon "00:01:00", kernel, noclear
You can also use:
sp_sysmon "00:01:00", noclear
You 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.
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'
In Adaptive Server version 15.0.1 and later, the default behavior of sp_sysmon is to not clear the monitor counters.
If you need to clear the monitor counters, use sp_sysmon with the clear option. For compatibility reasons, Adaptive Server accepts the noclear option as a valid parameter, but it does not affect the behavior of sp_sysmon.
However, if you run sp_sysmon using the begin_sample and end_sample options to begin and end the sample period, sp_sysmon always clears the monitor counters. Adaptive Server issues an error message if you run sp_sysmon with begin_sample or end_sample and the noclear option.
sp_sysmon displays information about Adaptive 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 values listed in Table 1-41 for the second parameter.
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.
Only a System Administrator can execute sp_sysmon.
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 |
|
Documentation “Monitoring performance with sp_sysmon” in the Performance and Tuning Guide.
