• Sybase sql stored procedures to use MDA tables

    May 1st, 2010 @ 11:56 PM by Jean-Francois LEBON | Be the 1st to comment

    MDA tables are very useful to understand what’s going on within your dataserver, so you can carry out accurate and successful performance and tuning campaigns. In this post I publish few basic Sybase stored procedures to use with MDA tables that focus on databases objects activity only (but I may release more MDA stored procedures in the future).

    If you haven’t installed yet MDA tables in your Sybase Adaptive Server, you can refer to my previous post HowTo install MonTables to install them.

    You can download the transact sql code here: sp_mda_stored_procedures.sql

    sp_mda_objiud: Top 20 tables having the most inserts, updates and deletes.

    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

    sp_mda_objlonglock: Top 20 tables having the longest lock detention.

    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

    sp_mda_objlr: Top 20 tables having the most logical reads.

    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

    sp_mda_objprw: Top 20 tables having the most physical reads/writes.

    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

    Tags: , , ,