use sybsystemprocs
go

if (select count(name) from sybsystemprocs..sysobjects
where type='P'
and name="sp_mda_objiud" ) =1
 begin
  print 'DROPPING STORED PROCEDURE sp_mda_objiud'
  drop procedure sp_mda_objiud
 end
go

print 'CREATING STORED PROCEDURE sp_mda_objiud'
go

create procedure sp_mda_objiud as
set nocount on

-- Never use it inside a transaction !
if @@trancount > 0
begin
 print "WARNING: You can NOT use this procedure inside a transaction."
 return (1)
end

declare @today datetime

select @today=getdate()
print "-- Top 20 objects with the most inserts, updates and deletes"
print "-- Today: %1! - Dataserver: %2!",@today,@@servername

select rank = identity(10), dbname = db_name(DBID),
objectname = object_name(ObjectID, DBID), IndexID, RowsInserted
into #itop
from master..monOpenObjectActivity
order by RowsInserted desc
select * from #itop where rank < 21 order by rank
drop table #itop

select rank = identity(10), dbname = db_name(DBID),
objectname = object_name(ObjectID, DBID), IndexID, RowsUpdated
into #utop
from master..monOpenObjectActivity
order by RowsUpdated desc
select * from #utop where rank < 21 order by rank
drop table #utop

select rank = identity(10), dbname = db_name(DBID),
objectname = object_name(ObjectID, DBID), IndexID, RowsDeleted
into #dtop
from master..monOpenObjectActivity
order by RowsDeleted desc

select * from #dtop where rank < 21 order by rank
drop table #dtop
go

grant execute on sp_mda_objiud to sa
go


use sybsystemprocs
go

if (select count(name) from sybsystemprocs..sysobjects
where type='P'
and name="sp_mda_objlonglock" ) =1
begin    
 print 'DROPPING STORED PROCEDURE sp_mda_objlonglock'
 drop procedure sp_mda_objlonglock
end
go

print 'CREATING STORED PROCEDURE sp_mda_objlonglock'
go


create procedure sp_mda_objlonglock as
set nocount on

-- Never use it inside a transaction !
if @@trancount > 0
begin
 print "WARNING: You can NOT use this procedure inside a transaction."
 return (1)
end

declare @today datetime
select @today=getdate()
print "--  Top 20 objects with longest lock detention"
print "--  Today: %1! - Dataserver: %2!",@today,@@servername

select rank = identity(10), dbname = db_name(DBID),
objectname = object_name(ObjectID, DBID), IndexID, LockWaits
into #ltop
from master..monOpenObjectActivity
where LockWaits >0 order by LockWaits desc

select * from #ltop where rank < 21 order by rank
drop table #ltop
go

grant execute on sp_mda_objlonglock to sa
go


use sybsystemprocs
go

if (select count(name) from sybsystemprocs..sysobjects
where type='P'
and name="sp_mda_objlr" ) =1
begin
        print 'DROPPING STORED PROCEDURE sp_mda_objlr'
        drop procedure sp_mda_objlr
end
go

print 'CREATING STORED PROCEDURE sp_mda_objlr'
go

create procedure sp_mda_objlr as
set nocount on
-- Never use it inside a transaction !
if @@trancount > 0
begin
 print "WARNING: You can NOT use this procedure inside a transaction."
 return (1)
end
declare @today datetime
select @today=getdate()
print "--  Top 20 objects with the most logical reads"
print "--  Today: %1! - Dataserver: %2!",@today,@@servername

select rank = identity(10), dbname = db_name(DBID),
objectname = object_name(ObjectID, DBID), IndexID, LogicalReads
into #lrtop
from master..monOpenObjectActivity order by LogicalReads desc
select * from #lrtop where rank < 21 order by rank
drop table #lrtop
go

grant execute on sp_mda_objlr to sa
go


use sybsystemprocs
go

if (select count(name) from sybsystemprocs..sysobjects
where type='P'
and name="sp_mda_objprw" ) =1
begin
        print 'DROPPING STORED PROCEDURE sp_mda_objprw'
        drop procedure sp_mda_objprw
end
go

print 'CREATING STORED PROCEDURE sp_mda_objprw'
go

create procedure sp_mda_objprw as
set nocount on
-- Never use it inside a transaction !
if @@trancount > 0
begin
 print "WARNING: You can NOT use this procedure inside a transaction."
 return (1)
end
declare @today datetime
select @today=getdate()
print "--  Top 20 objects with the most physical reads/writes"
print "--  Today: %1! - Dataserver: %2!",@today,@@servername

select rank=identity(10),dbname =  db_name(DBID),
objectname = object_name(ObjectID, DBID),
IndexID, PhysicalReads, PhysicalWrites
into #prtop
from master..monOpenObjectActivity order by PhysicalReads desc
select * from #prtop where rank < 21 order by rank
drop table #prtop
go

grant execute on sp_mda_objprw to sa
go