监控调优Sybase，介绍一下其辅助的存储过程：sp_monitor和sp_sysmon

存储过程sp_monitor，直接见例1或'help'的用法帮助：

例1：

1>sp_monitor connection, 'cpu'
2>go

结果：

spid LoginName ElapsedTime CPU_Time Physical_Reads LocksHeld SQLText
---- --------- ----------- -------- -------------- --------- -------
。。。。。。

稍为详细一些的用法帮助见下：（极详细的用法帮助请再在后面加上",all")

1> sp_monitor 'help'
2> go
Usage: sp_monitor [ 'enable' | 'disable' | 'connection' | 'procedure' |
'statement' | 'event' | 'deadlock' | 'procstack' | 'archive' | 'report' [, ...]
]
Usage: sp_monitor help [, 'enable' | 'disable' | 'connection' | 'procedure' |
'statement' | 'event' | 'deadlock' | 'procstack' | 'archive' | 'report']
Usage: sp_monitor help, 'all'

Usage: sp_monitor [ connection, [ cpu | diskio | elapsed time]]
Examples: sp_monitor 'connection', 'elapsed time'

Usage: sp_monitor [ event, [spid]]
Examples: sp_monitor 'event', '5'

Usage: sp_monitor [ procedure, [ dbname , [ procname, [, summary | detail]]]]
Examples: sp_monitor 'procedure'
Examples: sp_monitor 'procedure', 'employee_db', 'sp_get_salary'
Examples: sp_monitor 'procedure','detail'
Examples: sp_monitor 'procedure', 'employee_db', 'sp_get_employee_id', 'detail'

Usage: sp_monitor [ enable ]
Examples: sp_monitor 'enable'

Usage: sp_monitor [ disable ]
Examples: sp_monitor 'disable'

Usage: sp_monitor [ statement, [ cpu | diskio | elapsed time]]
Examples: sp_monitor 'statement', 'elapsed time'

Usage: sp_monitor 'deadlock' [ [, '@filters' ] [, '@output_modes' ] ]
Usage: sp_monitor 'deadlock'
                [  [, '<deadlockID>' | '<for Date>' ]
                   [, { 'verbose' | 'pagediag' }
                     |{   'count by date'
                        | 'count by application'
                        | 'count by object'

                        | 'count by date, object'
                      } ]
                ]

Usage: sp_monitor 'procstack' [, '<spid>'] [, '<contextblock>' ]

Usage: sp_monitor 'archive [using prefix=<string>]' {, '<monitoring_type>' }
Archiving is currently supported for only these monitoring types: 'deadlock'.

Usage: sp_monitor 'report [using prefix=<string>]' , '<monitoring_type>'
                [
<options supported for monitoring_type> ]
Reporting from an archive is currently supported for only these monitoring
types: 'deadlock'.
(return status = 0)

二、存储过程sp_sysmon

可用监控的模块名称(model_name)约有二十个，见下：

'kernel','wpm','parallel','taskmgmt','appmgmt','esp','housekeeper','monaccess','

xactsum','xactmgmt','indexmgmt', 'mdcache','locks','dcache','pcache','memory','re

covery','diskio','netio', 'cache wizard'

（具体意思可参考：http://www.sybasebbs.com/sybase/viewthread.php?tid=4219）

用法：sp_sysmon "hh:mm:ss", model_name

可全部监控使用：sp_sysmon "00:10:00"

例2：

1>sp_sysmon "00:10:00", kernel

2>go

结果:

===============================================================================
      Sybase Adaptive Server Enterprise System Performance Report
===============================================================================
 
Server Version:        Adaptive Server Enterprise/15.0.2/EBF 15961 ESD#6/P/RS60
Server Name:           SVC                                                     
Run Date:              Sep 02, 2009                                            
Sampling Started at:   Sep 02, 2009 19:19:53                                   
Sampling Ended at:     Sep 02, 2009 19:25:53                                   
Sample Interval:       00:06:00                                                
Sample Mode:           No Clear                                                
Counters Last Cleared: Jul 23, 2009 08:51:40                                   
 
===============================================================================
 
Kernel Utilization
------------------
 
  Your Runnable Process Search Count is set to 2000                            
  and I/O Polling Process Count is set to 10                                   
 
  Engine Busy Utilization        CPU Busy   I/O Busy       Idle                
  ------------------------       --------   --------   --------   
    Engine 0                       58.5 %      0.2 %     41.2 %             
    Engine 1                       59.4 %      0.1 %     40.5 %             
    Engine 2                       59.9 %      0.1 %     40.0 %             
    Engine 3                       60.7 %      0.2 %     39.0 %             
    Engine 4                       56.4 %      0.5 %     43.1 %             
    Engine 5                       56.7 %      0.2 %     43.2 %             
    Engine 6                       53.2 %      0.3 %     46.6 %             
  ------------------------       --------   --------   --------   
  Summary           Total         404.7 %      1.7 %    293.6 %             
                  Average          57.8 %      0.2 %     41.9 %             
 
  CPU Yields by Engine            per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
    Engine 0                         17.4           0.5        6258      12.6 %
    Engine 1                         17.2           0.5        6208      12.5 %
    Engine 2                         18.4           0.5        6615      13.3 %
    Engine 3                         18.9           0.6        6812      13.7 %
    Engine 4                         21.8           0.6        7840      15.8 %
    Engine 5                         20.7           0.6        7455      15.0 %
    Engine 6                         23.4           0.7        8406      16.9 %
  -------------------------  ------------  ------------  ----------
  Total CPU Yields                  137.8           4.1       49594            
 
  Network Checks
    Non-Blocking                 176005.3        5185.1    63361907      99.9 %
    Blocking                        137.4           4.0       49454       0.1 %
  -------------------------  ------------  ------------  ----------
  Total Network I/O Checks       176142.7        5189.1    63411361            
  Avg Net I/Os per Check              n/a           n/a     0.00672       n/a  
 
  Disk I/O Checks
    Total Disk I/O Checks        178994.1        5273.1    64437862       n/a  
    Checks Returning I/O           3585.2         105.6     1290682       2.0 %
    Avg Disk I/Os Returned            n/a           n/a     0.02796       n/a  
 
 呵，当然，这些都是在使用isql登录进Sybase后的操作，或者用DBArtisan连接Sybase后。

调优可使用sp_sysmon "00:10:00"的分析结果中带的建议方法。

另学习可参考一下：http://www.itpub.net/thread-207681-1-1.html

http://www.itpub.net/thread-1025687-1-1.html