Home » Databases » Sybase » ASE » Ed Barlow Stored Procedures: sp__monwaits updated with formatting for SAP Sybase ASE 15 and higher

Ed Barlow Stored Procedures: sp__monwaits updated with formatting for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__monwaits stored procedure for displaying the wait times since server start in Sybase ASE as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

The sp__monwaits often truncated the event description making it rather difficult to determine exactly which event occurred. I’ve expanded the size the field to reflect the length of the longest event description dynamically.

 Event                                    WaitTime    Waits
 ---------------------------------------- ----------- -----------
 xact coord: pause during idle loop           4001410       66688
 wait for buffer read to complete              532248    85475068
 wait for buffer write to complete              32235    15923057
 wait for buffer validation to complete          3680      162024
 wait for mass to stop changing                 30267    10315571
 wait for mass to finish changing               10774   502746724
 wait to acquire latch                          55839     7807761
 waiting for disk write to complete            115108    40656343
 waiting for disk write to complete             60530    11697654
 waiting for disk write to complete             27566     9398819
 waiting for disk write to complete             85976    50951106
 checkpoint process idle loop                  951594       16619
 hk: pause for some time                      2713933      530621
 wait for flusher to queue full DFLPIECE       151633      150265
 wait for data from client                       1933        6000
 wait until an engine has been offlined       1000427       33342
 wait for someone else to finish reading       372601    55500541
 waiting for semaphore                         391902    13636318
 waiting for CTLIB event to complete            87265   112396697
 waiting while allocating new client sock      997833       97155
 waiting while no network read or write i    11936037  1037354467
 waiting on run queue after yield              295191    72098512
 waiting on run queue after sleep              746636 -1313156962
 replication agent sleeping in retry slee        3839          64
 replication agent sleeping during flush      1840806    12733523
 waiting for incoming network data          201256524   265587239
 waiting for network send to complete         1175318   747115161
 waiting until last chance threshold is c        1144           3
 waiting for date or time in waitfor comm       68630         210

to

 Event                                              WaitTime    Waits
 -------------------------------------------------- ----------- -----------
 waiting for incoming network data                    221594153    12452930
 waiting for client connection request                  2760845      207231
 hk: pause for some time                                2754002      443443
 xact coord: pause during idle loop                      920462       15341
 wait until an engine has been offlined                  920462       30682
 Wait until heartbeat or check interval expires          920399        1534
 checkpoint process idle loop                            919400       18224
 replication agent sleeping during flush                 777679       91871
 replication agent sleeping in retry sleep               138301        2305
 wait for flusher to queue full DFLPIECE                  31029       31055
 waiting for regular buffer read to complete              11461    37289445
 waiting for last i/o on MASS to complete                  8079     2026180
 waiting on run queue after yield                          4914     4974455
 waiting on run queue after sleep                          3652    71141496
 wait for mass read to finish when getting page            3341     5365397
 wait for i/o to finish after writing last log page        2757     3624230
 waiting for network send to complete                      1404       58634
 waiting for buf write to complete before writing          1334     1452867

Differences:

$ diff sp__monwaits.old sp__monwaits.15
1,13d0
< <
< /*
< select distinct SPID,Id=substring(Login+"("+Application+")",1,30),SecondsWaiting=sum(SecondsWaiting),"Non Network Wait Reason"=c.Description
< from master..monProcess p,master..monWaitClassInfo c, master..monWaitEventInfo i
< where p.WaitEventID = i.WaitEventID
< and   i.WaitClassID = c.WaitClassID
< and   c.Description!="waiting for input from the network"
< group by c.WaitClassID,SPID
< order by SPID
< */
<
<
22d8
<
32a19,21
> declare @max_eventstr_size varchar(4)
> declare @exec_str varchar(2000)
>
34c23,25
< select "Event"=substring(i.Description,1,40),WaitTime,Waits
---
> begin
>       select "Event" = i.Description, WaitTime, Waits
>     into #tmp_nodelay
37a29,42
>
>     if @dont_format is null
>     begin
>         select @max_eventstr_size = convert(varchar(3), isnull(max(char_length(Event)), 1)) from #tmp_nodelay
>         select @exec_str = 'select "Event" = convert(varchar(' + @max_eventstr_size + '), Event), WaitTime, Waits from #tmp_nodelay order by WaitTime desc'
>         exec (@exec_str)
>     end
>     else
>     begin
>         select * from #tmp_nodelay order by WaitTime desc
>     end
>
>     delete #tmp_nodelay
> end
40c45
< select "Event"=substring(i.Description,1,40),WaitTime,Waits,s.WaitEventID
---
>       select "Event" = i.Description, WaitTime, Waits, s.WaitEventID
51a57
>
56,63c62
< select "Time"=convert(varchar(8),getdate(),8),
<                       "Event"=i.Event,
<                       WaitTime=s.WaitTime-i.WaitTime,
<                       Waits=s.Waits-i.Waits
<               from #tmp i, master..monSysWaits s
<               where (s.WaitTime>i.WaitTime or s.Waits>i.Waits)
< and s.WaitEventID= i.WaitEventID
<               order by WaitTime desc
---
>         select @max_eventstr_size = convert(varchar(3), isnull(max(char_length(Event)), 1)) from #tmp
64a64,73
>               select @exec_str = 'select "Time" = convert(varchar(8),getdate(),8),
>                          "Event" = convert(varchar(' + @max_eventstr_size + '), rtrim(i.Event)),
>                          WaitTime = s.WaitTime-i.WaitTime,
>                          Waits = s.Waits-i.Waits
>                  from #tmp i, master..monSysWaits s
>                  where (s.WaitTime > i.WaitTime or s.Waits > i.Waits)
>                  and s.WaitEventID= i.WaitEventID
>                  order by WaitTime desc'
>         exec (@exec_str)
>
68c77
< select "Event"=substring(i.Description,1,40),WaitTime,Waits,s.WaitEventID
---
>               select "Event"=i.Description, WaitTime, Waits, s.WaitEventID
70c79
< where s.WaitEventID= i.WaitEventID
---
>               where s.WaitEventID = i.WaitEventID

Full SQL text of sp__monwaits:

use sybsystemprocs
go
/* Procedure library copyright(c) 2004 by Edward M Barlow */

IF EXISTS (SELECT * FROM sysobjects
           WHERE  name = "sp__monwaits"
           AND    type = "P")
   DROP PROC sp__monwaits
go

CREATE PROC sp__monwaits(
                @num_sec_delay int=NULL,
                @num_iter int=NULL,
                @dont_format char(1)=NULL)
AS
set nocount on
declare @delay char(8)

declare @max_eventstr_size varchar(4)
declare @exec_str varchar(2000)

if @num_sec_delay is null
begin
        select "Event" = i.Description, WaitTime, Waits
    into #tmp_nodelay
        from master..monWaitEventInfo i, master..monSysWaits s
        where s.WaitEventID= i.WaitEventID
        and     WaitTime>1000

    if @dont_format is null
    begin
        select @max_eventstr_size = convert(varchar(3), isnull(max(char_length(Event)), 1)) from #tmp_nodelay
        select @exec_str = 'select "Event" = convert(varchar(' + @max_eventstr_size + '), Event), WaitTime, Waits from #tmp_nodelay order by WaitTime desc'
        exec (@exec_str)
    end
    else
    begin
        select * from #tmp_nodelay order by WaitTime desc
    end

    delete #tmp_nodelay
end
else
begin
        select "Event" = i.Description, WaitTime, Waits, s.WaitEventID
        into #tmp
        from master..monWaitEventInfo i, master..monSysWaits s
        where s.WaitEventID= i.WaitEventID

        if @num_sec_delay<10
                select @delay="00:00:0"+convert(char(1),@num_sec_delay)
        else
                select @delay="00:00:"+convert(char(2),@num_sec_delay)

        if @num_iter is null
                select @num_iter=100

        while @num_iter>0
        begin
                waitfor delay @delay

        select @max_eventstr_size = convert(varchar(3), isnull(max(char_length(Event)), 1)) from #tmp

                select @exec_str = 'select "Time" = convert(varchar(8),getdate(),8),
                           "Event" = convert(varchar(' + @max_eventstr_size + '), rtrim(i.Event)),
                           WaitTime = s.WaitTime-i.WaitTime,
                           Waits = s.Waits-i.Waits
                   from #tmp i, master..monSysWaits s
                   where (s.WaitTime > i.WaitTime or s.Waits > i.Waits)
                   and s.WaitEventID= i.WaitEventID
                   order by WaitTime desc'
        exec (@exec_str)

                delete #tmp

                insert #tmp
                select "Event"=i.Description, WaitTime, Waits, s.WaitEventID
                from master..monWaitEventInfo i, master..monSysWaits s
                where s.WaitEventID = i.WaitEventID

                select @num_iter = @num_iter - 1
        end
end

return

go

GRANT EXECUTE ON sp__monwaits  TO public
go

sp__monwaits

Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect