Ed Barlow Stored Procedures: sp__monobj Updated with formatting for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__monobj uses a heuristic algorithm to find which objects are most busy as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

I’ve cleaned up the format output

Differences:
diff sp__monobj.old sp__monobj.15
17a18,26
> ————————————————————————————————–
> — Vers| Date | Who | DA | Description
> ——-+———-+——————–+—-+—————————————————–
> — 1.1 |11/20/2013| Jason Froebe | | Fix formatting of outputs
> — 1.0 | 2006 | Edward Barlow | | Shows Highest Usage Objects Based On Mda tables.
> — | | | | This uses a heuristic algorithm to find what objects
> — | | | | are most busy.
> ——-+———-+——————–+—-+—————————————————–
>
21a31,34
> declare @max_objectname_size varchar(3)
> declare @exec_str varchar(2000)
>
>
34a48,51
> print “”
> print ” score = LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + Lockwaits”
> print “”
>
36,37c53,55
< select ObjName=convert(varchar(40),db_name(DBID)+"."+object_name(ObjectID,DBID)), < score= LogicalReads/100 + --- >
> select ObjName = db_name(DBID) + “..” + object_name(ObjectID, DBID),
> score = LogicalReads/100 +
45,46c63,65
< from master..monOpenObjectActivity < where IndexID=0 --- > into #busy_report
> from master..monOpenObjectActivity
> where IndexID=0
55c74
< and db_name(DBID)!="tempdb" --- > and DBName !=”tempdb”
57a77
>
58a79,87
>
> select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report
>
> select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName), score
> from #busy_report
> order by score desc’
>
> exec (@exec_str)
>
72a102,121
> create table #busy_report_iter_obj (
> ObjName varchar(255) not null,
> Op bigint not null,
> LogReads int not null,
> PhysReads int not null,
> PageReads int not null,
> Writes int not null,
> Ins int not null,
> Del int not null,
> Upd int not null,
> Locks int not null,
> LockWt int not null)
>
> create table #busy_report_iter (
> ObjName varchar(255) not null,
> Op int not null,
> Reads int not null,
> Writes int not null,
> NumRows bigint not null)
>
81,86c130,131
< -- select * < -- from master..monOpenObjectActivity < -- where IndexID=0 and db_name(DBID)!='tempdb' < -- and ( @object_name is null or @object_name=object_name(ObjectID, DBID)) < < select distinct ObjName=convert(varchar(39),db_name(o.DBID)+"."+object_name(o.ObjectID,o.DBID)), --- > insert into #busy_report_iter_obj
> select distinct ObjName = db_name(o.DBID) + “..” + object_name(o.ObjectID,o.DBID),
88c133
< LogReads=o.LogicalReads - i.LogicalReads, --- > LogReads=o.LogicalReads – i.LogicalReads,
92,103c137,167
< "Ins"=o.RowsInserted -i.RowsInserted, < "Del"= o.RowsDeleted - i.RowsDeleted, < "Upd"= o.RowsUpdated - i.RowsUpdated , < "Locks"= o.LockRequests - i.LockRequests, < "LockWt"= o.LockWaits - i.LockWaits < from master..monOpenObjectActivity o,#tmp i < where o.IndexID=i.IndexID < and o.ObjectID=i.ObjectID < and o.DBID=i.DBID < and o.IndexID=i.IndexID < and o.IndexID=0 < and i.IndexID=0 --- > Ins=o.RowsInserted -i.RowsInserted,
> Del= o.RowsDeleted – i.RowsDeleted,
> Upd= o.RowsUpdated – i.RowsUpdated ,
> Locks= o.LockRequests – i.LockRequests,
> LockWt= o.LockWaits – i.LockWaits
> from master..monOpenObjectActivity o,#tmp i
> where o.IndexID=i.IndexID
> and o.ObjectID=i.ObjectID
> and o.DBID=i.DBID
> and o.IndexID=i.IndexID
> and o.IndexID=0
> and i.IndexID=0
>
> select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter_obj
>
> select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName),
> Op,
> LogReads,
> PhysReads,
> PageReads,
> Writes,
> Ins,
> Del,
> Upd,
> Locks,
> LockWt
> from #busy_report_iter_obj’
>
> exec (@exec_str)
>
> delete #busy_report_iter_obj
107c171
< set rowcount 10 --- > set rowcount 10
109,138c173,202
< select distinct ObjName=convert(varchar(39),db_name(o.DBID)+"."+object_name(o.ObjectID,o.DBID)), < Op=o.Operations-i.Operations, < Reads=o.LogicalReads+ o.PhysicalReads- i.LogicalReads- i.PhysicalReads, < Writes=o.PhysicalWrites-i.PhysicalWrites, < "Rows"=o.RowsInserted + o.RowsDeleted + o.RowsUpdated-i.RowsInserted - i.RowsDeleted - i.RowsUpdated < from master..monOpenObjectActivity o,#tmp i < where o.IndexID=i.IndexID < and o.ObjectID=i.ObjectID < and o.DBID=i.DBID < and o.IndexID=i.IndexID < and o.IndexID=0 < and i.IndexID=0 < -- and ( o.LogicalReads>0 or o.LockRequests>0 )
< order by < o.LogicalReads/100 + < o.PhysicalReads/10 + < o.PhysicalWrites + < o.RowsInserted + < o.RowsDeleted + < o.RowsUpdated + < o.LockRequests/100 + < o.LockWaits - < i.LogicalReads/100 - < i.PhysicalReads/10 - < i.PhysicalWrites - < i.RowsInserted - < i.RowsDeleted - < i.RowsUpdated - < i.LockRequests/100 - < i.LockWaits desc --- > insert into #busy_report_iter
> select distinct ObjName = db_name(o.DBID) + “..” + object_name(o.ObjectID,o.DBID),
> Op = o.Operations-i.Operations,
> Reads = o.LogicalReads+ o.PhysicalReads- i.LogicalReads- i.PhysicalReads,
> Writes = o.PhysicalWrites-i.PhysicalWrites,
> NumRows = o.RowsInserted + o.RowsDeleted + o.RowsUpdated-i.RowsInserted – i.RowsDeleted – i.RowsUpdated
> from master..monOpenObjectActivity o,#tmp i
> where o.IndexID=i.IndexID
> and o.ObjectID=i.ObjectID
> and o.DBID=i.DBID
> and o.IndexID=i.IndexID
> and o.IndexID=0
> and i.IndexID=0
> order by
> o.LogicalReads/100 +
> o.PhysicalReads/10 +
> o.PhysicalWrites +
> o.RowsInserted +
> o.RowsDeleted +
> o.RowsUpdated +
> o.LockRequests/100 +
> o.LockWaits –
> i.LogicalReads/100 –
> i.PhysicalReads/10 –
> i.PhysicalWrites –
> i.RowsInserted –
> i.RowsDeleted –
> i.RowsUpdated –
> i.LockRequests/100 –
> i.LockWaits desc
139a204,215
> select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter
>
> select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName),
> Op,
> Reads,
> Writes,
> NumRows
> from #busy_report_iter’
>
> exec (@exec_str)
>
> delete #busy_report_iter
152d227
< -- select 1,* from #tmp where object_id('sp_aux_getsize')=ObjectID and DBID=db_id('master')[/diff] SQL of sp__monobj: [sql highlight_lines="18,19,20,21,22,23,24,25,31,32,33,34,54,55,63,64,65,74,79,80,81,82,83,84,85,86,102,103,104,105,106,107,108,109,110,111,112,113,115,116,117,118,119,120,130,131,133,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,204,205,206,207,208,209,210,211,212,213,214,215,227"]use sybsystemprocs go /* Procedure library copyright(c) 2004-2006 by Edward M Barlow */ IF EXISTS (SELECT * FROM sysobjects WHERE name = "sp__monobj" AND type = "P") DROP PROC sp__monobj go CREATE PROC sp__monobj( @num_sec_delay int=NULL, @num_iter int=NULL, @dont_format char(1)=NULL, @object_name varchar(30)=NULL) AS -------------------------------------------------------------------------------------------------- -- Vers| Date | Who | DA | Description -------+----------+--------------------+----+----------------------------------------------------- -- 1.1 |11/20/2013| Jason Froebe | | Fix formatting of outputs -- 1.0 | 2006 | Edward Barlow | | Shows Highest Usage Objects Based On Mda tables. -- | | | | This uses a heuristic algorithm to find what objects -- | | | | are most busy. -------+----------+--------------------+----+----------------------------------------------------- set nocount on declare @delay char(8) declare @objid int declare @max_objectname_size varchar(3) declare @exec_str varchar(2000) if @object_name is not null begin select @objid=ObjectID from master..monOpenObjectActivity where object_name(ObjectID,DBID)=@object_name if @objid is null return end if @num_sec_delay is null begin --Busy Object Report print "Top 20 Used Objects" print "" print " score = LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + Lockwaits" print "" set rowcount 20 select ObjName = db_name(DBID) + ".." + object_name(ObjectID, DBID), score = LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + LockWaits into #busy_report from master..monOpenObjectActivity where IndexID=0 and LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + LockWaits >1000
and DBName !=”tempdb”
and ( @object_name is null or @object_name=object_name(ObjectID, DBID))
order by score desc

set rowcount 0

select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report

select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName), score
from #busy_report
order by score desc’

exec (@exec_str)

end
else
begin
select *
into #tmp
from master..monOpenObjectActivity
where IndexID=0 and db_name(DBID)!=’tempdb’
and ( @object_name is null or ObjectID=@objid )

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)

create table #busy_report_iter_obj (
ObjName varchar(255) not null,
Op bigint not null,
LogReads int not null,
PhysReads int not null,
PageReads int not null,
Writes int not null,
Ins int not null,
Del int not null,
Upd int not null,
Locks int not null,
LockWt int not null)

create table #busy_report_iter (
ObjName varchar(255) not null,
Op int not null,
Reads int not null,
Writes int not null,
NumRows bigint not null)

if @num_iter is null
select @num_iter=100
while @num_iter>0
begin
waitfor delay @delay

if( @object_name is not null )
begin
insert into #busy_report_iter_obj
select distinct ObjName = db_name(o.DBID) + “..” + object_name(o.ObjectID,o.DBID),
Op=o.Operations-i.Operations,
LogReads=o.LogicalReads – i.LogicalReads,
PhysReads=o.PhysicalReads- i.PhysicalReads,
PageReads=o.PagesRead- i.PagesRead,
Writes=o.PhysicalWrites-i.PhysicalWrites,
Ins=o.RowsInserted -i.RowsInserted,
Del= o.RowsDeleted – i.RowsDeleted,
Upd= o.RowsUpdated – i.RowsUpdated ,
Locks= o.LockRequests – i.LockRequests,
LockWt= o.LockWaits – i.LockWaits
from master..monOpenObjectActivity o,#tmp i
where o.IndexID=i.IndexID
and o.ObjectID=i.ObjectID
and o.DBID=i.DBID
and o.IndexID=i.IndexID
and o.IndexID=0
and i.IndexID=0

select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter_obj

select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName),
Op,
LogReads,
PhysReads,
PageReads,
Writes,
Ins,
Del,
Upd,
Locks,
LockWt
from #busy_report_iter_obj’

exec (@exec_str)

delete #busy_report_iter_obj
end
else
begin
set rowcount 10

insert into #busy_report_iter
select distinct ObjName = db_name(o.DBID) + “..” + object_name(o.ObjectID,o.DBID),
Op = o.Operations-i.Operations,
Reads = o.LogicalReads+ o.PhysicalReads- i.LogicalReads- i.PhysicalReads,
Writes = o.PhysicalWrites-i.PhysicalWrites,
NumRows = o.RowsInserted + o.RowsDeleted + o.RowsUpdated-i.RowsInserted – i.RowsDeleted – i.RowsUpdated
from master..monOpenObjectActivity o,#tmp i
where o.IndexID=i.IndexID
and o.ObjectID=i.ObjectID
and o.DBID=i.DBID
and o.IndexID=i.IndexID
and o.IndexID=0
and i.IndexID=0
order by
o.LogicalReads/100 +
o.PhysicalReads/10 +
o.PhysicalWrites +
o.RowsInserted +
o.RowsDeleted +
o.RowsUpdated +
o.LockRequests/100 +
o.LockWaits –
i.LogicalReads/100 –
i.PhysicalReads/10 –
i.PhysicalWrites –
i.RowsInserted –
i.RowsDeleted –
i.RowsUpdated –
i.LockRequests/100 –
i.LockWaits desc

select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter

select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName),
Op,
Reads,
Writes,
NumRows
from #busy_report_iter’

exec (@exec_str)

delete #busy_report_iter
set rowcount 0

end

delete #tmp

insert #tmp
select *
from master..monOpenObjectActivity
where IndexID=0 and db_name(DBID)!=’tempdb’
and ( @object_name is null or ObjectID=@objid )

select @num_iter = @num_iter – 1
end
end

return

go

GRANT EXECUTE ON sp__monobj TO public
go[/sql]
sp__monwaits

Share Button

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

Mich Talebzadeh createdSAP Sybase the sp__monrunning stored procedure for displaying the procedures that are running for more than 100 ms as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

I’ve cleaned up the format output and added the dont_format option:

$ diff sp__monrunning.old sp__monrunning.15
9c9,10
< create procedure sp__monrunning
---
> create procedure sp__monrunning(
>                      @dont_format char(1)=NULL)
11,16c12,18
< ------------------------------------------------------------------------------------------------
< -- Vers|  Date  |      Who           | DA | Description
< -------+--------+--------------------+----+-----------------------------------------------------
< -- 1.0 |07/04/06|  Mich Talebzadeh   |    | Statistics on processes currently being executed
< --     |        |                    |    | with Elapsed time > 100ms
< -------+--------+--------------------+----+-----------------------------------------------------
---
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/20/2013|  Jason Froebe      |    | Cleaned up the report format, added dont_format option
> -- 1.0 |07/04/2006|  Mich Talebzadeh   |    | Statistics on processes currently being executed
> --     |          |                    |    | with Elapsed time > 100ms
> -------+--  ------+--------------------+----+-----------------------------------------------------
18,22c20,24
< declare @time datetime
<       select @time = getdate()
<       --print ""
<       --print "Stats for various procedures at %1!. Server up since %2!", @time, @@boottime
<       --print ""
---
>     declare @max_loginname_size varchar(3)
>     declare @max_procname_size varchar(3)
>     declare @max_dbname_size varchar(3)
>     declare @exec_str varchar(2000)
>
35c37
< "Name" = substring(suser_name(p.suid),1,20),
---
>               "Name" = suser_name(p.suid),
37,39c39,42
< "Procedure" = ProcName,
<               "Database" = DBNAME,
<               "Elapsed Time/ms" = TimeMs
---
>               ProcName,
>               DBNAME,
>               TimeMs
>     into #proc_report
43c46,75
< order by TimeMs asc
---
>
>     select @max_loginname_size = convert(varchar(3), isnull( max(char_length(Name)), 1)),
>         @max_procname_size = convert(varchar(3), isnull( max(char_length(ProcName)), 1)),
>         @max_dbname_size = convert(varchar(3), isnull( max(char_length(DBNAME)), 1))
>     from #proc_report
>
>     if @dont_format is null
>     begin
>         select @exec_str = 'select
>             "Name" = convert(varchar(' + @max_loginname_size + '), Name),
>                   SPID,
>                   "Procedure" = convert(varchar(' + @max_procname_size + '), ProcName),
>                   "Database" = convert(varchar(' + @max_dbname_size + '), DBNAME),
>                   "Elapsed Time/ms" = TimeMs
>         from #proc_report
>           order by TimeMs asc'
>
>         exec (@exec_str)
>     end
>     else
>     begin
>         select
>             "Name" = Name,
>                   SPID,
>                   "Procedure" = ProcName,
>                   "Database" = DBNAME,
>                   "Elapsed Time/ms" = TimeMs
>         from #proc_report
>           order by TimeMs asc
>     end

Full SQL code of sp__monrunning:

use sybsystemprocs
go

IF EXISTS (SELECT * FROM sysobjects
           WHERE  name = "sp__monrunning"
           AND    type = "P")
   DROP PROC sp__monrunning
go
create procedure sp__monrunning(
                     @dont_format char(1)=NULL)
as
--------------------------------------------------------------------------------------------------
-- Vers|   Date   |      Who           | DA | Description
-------+----------+--------------------+----+-----------------------------------------------------
-- 1.1 |11/20/2013|  Jason Froebe      |    | Cleaned up the report format, added dont_format option
-- 1.0 |07/04/2006|  Mich Talebzadeh   |    | Statistics on processes currently being executed
--     |          |                    |    | with Elapsed time > 100ms
-------+--  ------+--------------------+----+-----------------------------------------------------
begin
    declare @max_loginname_size varchar(3)
    declare @max_procname_size varchar(3)
    declare @max_dbname_size varchar(3)
    declare @exec_str varchar(2000)

        select
                SPID,
                ProcName = isnull(object_name(ProcedureID, DBID),"UNKNOWN"),
                DBNAME = isnull(db_name(DBID), "UNKNOWN"),
                TimeMs = datediff(ms, min(StartTime), max(EndTime))
                into #performance
                from master..monSysStatement m
        where db_name(DBID) != 'sybsystemprocs'
        group by SPID, DBID, ProcedureID, BatchID
        having ProcedureID != 0

        select distinct
                "Name" = suser_name(p.suid),
                SPID,
                ProcName,
                DBNAME,
                TimeMs
    into #proc_report
        from #performance t, master..sysprocesses p
        where t.SPID = p.spid
        and TimeMs >= 100

    select @max_loginname_size = convert(varchar(3), isnull( max(char_length(Name)), 1)),
        @max_procname_size = convert(varchar(3), isnull( max(char_length(ProcName)), 1)),
        @max_dbname_size = convert(varchar(3), isnull( max(char_length(DBNAME)), 1))
    from #proc_report

    if @dont_format is null
    begin
        select @exec_str = 'select
            "Name" = convert(varchar(' + @max_loginname_size + '), Name),
                    SPID,
                    "Procedure" = convert(varchar(' + @max_procname_size + '), ProcName),
                    "Database" = convert(varchar(' + @max_dbname_size + '), DBNAME),
                    "Elapsed Time/ms" = TimeMs
        from #proc_report
            order by TimeMs asc'

        exec (@exec_str)
    end
    else
    begin
        select
            "Name" = Name,
                    SPID,
                    "Procedure" = ProcName,
                    "Database" = DBNAME,
                    "Elapsed Time/ms" = TimeMs
        from #proc_report
            order by TimeMs asc
    end
end
go
grant exec on sp__monrunning to public
go
exit

sp__monrunning

Share Button

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&lt;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

Ed Barlow Stored Procedures: sp__helprotect updated for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__helprotect stored procedure for extracting permissions in Sybase ASE as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures). I’ve updated it for v15 and higher:

$ diff helpprotect.10 helpprotect.15
26a23,29
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/18/2013|  Jason Froebe      |    | Updated for ASE v15
> -- 1.0 |          |  Edward M Barlow   |    | Stored procedure extracting permissions for objects
> -------+----------+--------------------+----+-----------------------------------------------------
>
135a139
> -- protecttype column can contain these values: 0 for grant with grant. 1 for grant. 2 for revoke
137c141,152
< set     ending = " WITH GRANT OPTION", protecttype_text="GRANT"
---
> set     protecttype_text =
>    case
>      when protecttype = 0
>        then "GRANT"
>      when protecttype = 1
>        then "GRANT"
>      when protecttype = 2
>        then "REVOKE"
>    end
>
> update  #protects
> set     ending = " WITH GRANT OPTION"
145c160,171
< select substring(rtrim(protecttype_text)+" "+rtrim(action_text)+" on "+rtrim(object_name(id))+column_name+" to "+rtrim(user_name(uid))+ending,1,59)
---
>         select
>          substring(
>            rtrim(protecttype_text)
>            + " "
>            + rtrim(action_text)
>            + case when id = 0 then " " else " on " end
>            + rtrim(object_name(id))
>            + column_name
>            + " to "
>            + rtrim(user_name(uid))
>            + ending
>          ,1,59)
146a173
>         where rtrim(action_text) != ""
149c176,187
< select substring(rtrim(protecttype_text)+" "+rtrim(action_text)+" on "+rtrim(object_name(id))+column_name+" to "+rtrim(user_name(uid))+ending,1,79)
---
>         select
>          substring(
>            rtrim(protecttype_text)
>            + " "
>            + rtrim(action_text)
>            + case when id = 0 then " " else " on " end
>            + rtrim(object_name(id))
>            + column_name
>            + " to "
>            + rtrim(user_name(uid))
>            + ending
>          ,1,79)
150a189
>         where rtrim(action_text) != ""
153c192,203
< select substring(rtrim(protecttype_text)+" "+rtrim(action_text)+" on "+rtrim(object_name(id))+column_name+" to "+rtrim(user_name(uid))+ending,1,131)
---
>         select
>          substring(
>            rtrim(protecttype_text)
>            + " "
>            + rtrim(action_text)
>            + case when id = 0 then " " else " on " end
>            + rtrim(object_name(id))
>            + column_name
>            + " to "
>            + rtrim(user_name(uid))
>            + ending
>          ,1,131)
154a205
>         where rtrim(action_text) != ""
/* Procedure copyright(c) 1995 by Edward M Barlow */
/*  Stored Procedure is under GPL v2 */

/******************************************************************************
**
** Name        : sp__helpprotect.sql
**
******************************************************************************/
if exists (select * from sysobjects
           where  name = "sp__helprotect"
           and    type = "P")
begin
   drop proc sp__helprotect
end
go

create procedure sp__helprotect
        @parameter varchar(30) = NULL            /* name of object or user to check     */,
        @do_system_tables char(1) = null, /* if not null will include system tbls */
        @dont_format char(1) = null,
        @groups_only char(1) = null
as
--------------------------------------------------------------------------------------------------
-- Vers|   Date   |      Who           | DA | Description
-------+----------+--------------------+----+-----------------------------------------------------
-- 1.1 |11/18/2013|  Jason Froebe      |    | Updated for ASE v15
-- 1.0 |          |  Edward M Barlow   |    | Stored procedure extracting permissions for objects
-------+----------+--------------------+----+-----------------------------------------------------

        declare @type char(2), @uid int, @msg varchar(255), @objid int

        if @parameter is NULL
                select @objid=null
        else
                select @objid = object_id(@parameter)

        /* define our table */
        select   id,uid,action,protecttype,columns,grantor,
                        column_name             = "                               "
                        ,action_text            = "                               "
                        ,protecttype_text = "                               "
                        ,ending                                 = "                               "
        into    #protects
        from    sysprotects
        where 1=2

        /* Either a passed object or all objects */
        if @objid is not null or @parameter is null
        begin

                select uid,gid into #groups from sysusers

                if @groups_only is not null
                        delete  #groups
                        where    uid != gid

                /* IT IS AN OBJECT */
                insert  #protects
                select  id,p.uid,action,protecttype,columns,grantor,"","","",""
                from    sysprotects p, #groups g
                where   id=isnull(@objid,id)
                and      p.uid = g.uid

                /* REVOKES ON COLUMNS */
                insert  #protects
                select  id,p.uid,action,2,columns,grantor,
                        "("+col_name(p.id,c.number)+")","","",""
                from            sysprotects p, master.dbo.spt_values c, #groups g
                where   p.columns is not null
                and             convert(tinyint,substring(p.columns,c.low,1)) & c.high=0
                and             c.type = "P"
                and             c.number < = 255
                and             c.number>0
                and             c.low>1
                and             col_name(p.id,c.number) is not null
                and             id=isnull(@objid,id)
                and      p.uid=g.uid

                if @do_system_tables is null and @objid is null
                        delete #protects
                        from   #protects p, sysobjects o
                        where  p.id = o.id
                        and    o.type = 'S'
        end
        else
        begin

                /* IS IT A USER */
                select @uid = uid from sysusers where name=@parameter
                if @@rowcount = 0 or @uid is null
                begin
                   print "No User Or Object Found"
                   return (1)
                end

                insert  #protects
                select  distinct id,uid,action,protecttype,columns,grantor,"","","",""
                from    sysprotects p
                where   uid=@uid
                /* and          isnull( p.columns,0x01 ) = 0x01 */

                /* REVOKES ON COLUMNS */
                insert  #protects
                select  id,uid,action,2,columns,grantor,
                        "("+col_name(p.id,c.number)+")", "","",""
                from    sysprotects p, master.dbo.spt_values c
                where isnull( p.columns,0x01 ) != 0x01
                and     convert(tinyint, substring(p.columns, c.low, 1)) & c.high = 0
                and     c.type = "P"
                and     c.number < = 255
                and     c.number>0
                and     c.low>1
                and     col_name(p.id,c.number) is not null
                and     uid=@uid

                if @do_system_tables is null
                        delete #protects
                        from   #protects p, sysobjects o
                        where  p.id = o.id
                        and    o.type = 'S'
        end

/* References etc */
delete  #protects
where   action in(151,207,222,233,236)

update  #protects
set     action_text = name
from    master.dbo.spt_values v
where   v.type='T'
and     v.number = #protects.action

update  #protects
set     protecttype_text = name
from    master.dbo.spt_values v
where   v.type='T'
and     v.number = #protects.protecttype +204

-- protecttype column can contain these values: 0 for grant with grant. 1 for grant. 2 for revoke
update  #protects
set     protecttype_text =
   case
     when protecttype = 0
       then "GRANT"
     when protecttype = 1
       then "GRANT"
     when protecttype = 2
       then "REVOKE"
   end

update  #protects
set     ending = " WITH GRANT OPTION"
where   protecttype = 0

declare @max_len int
select @max_len = max(char_length( rtrim(protecttype_text)+" "+rtrim(action_text)+" on "+rtrim(object_name(id))+column_name+" to "+rtrim(user_name(uid))+ending))
from #protects

if @max_len < 60
        select
         substring(
           rtrim(protecttype_text)
           + " "
           + rtrim(action_text)
           + case when id = 0 then " " else " on " end
           + rtrim(object_name(id))
           + column_name
           + " to "
           + rtrim(user_name(uid))
           + ending
         ,1,59)
        from #protects
        where rtrim(action_text) != ""
        order by object_name(id),protecttype_text
else if @max_len < 80
        select
         substring(
           rtrim(protecttype_text)
           + " "
           + rtrim(action_text)
           + case when id = 0 then " " else " on " end
           + rtrim(object_name(id))
           + column_name
           + " to "
           + rtrim(user_name(uid))
           + ending
         ,1,79)
        from #protects
        where rtrim(action_text) != ""
        order by object_name(id),protecttype_text
else if @max_len < 132
        select
         substring(
           rtrim(protecttype_text)
           + " "
           + rtrim(action_text)
           + case when id = 0 then " " else " on " end
           + rtrim(object_name(id))
           + column_name
           + " to "
           + rtrim(user_name(uid))
           + ending
         ,1,131)
        from #protects
        where rtrim(action_text) != ""
        order by object_name(id),protecttype_text


return (0)
go
grant execute on sp__helprotect to public
go

helpprotect

Share Button