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

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

Leave a Reply

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

*
*

Facebook login by WP-FB-AutoConnect