About MDA table.

[Former Member]

Hi all,

My ASE version is ASE15.0.x and ASE15.7, I had create a store procedure to select the online sql:

create proc sp_mon

as

select s.SPID,"login"=convert(char(10),suser_name(l.suid)), "ip"=convert(char(15),l.ipaddr) , "DB"=convert(char(10),db_name(l.dbid)) ,

"cmd"=convert(char(15),l.cmd) ,"status"=convert(char(10),l.status) ,s.CpuTime, s.PhysicalReads, s.LogicalReads,  t.SQLText 

from master..monProcessStatement s, master..monProcessSQLText t , master..sysprocesses l

where s.SPID = t.SPID 

and s.SPID =  l.spid

and l.spid != @@spid

order by s.SPID, t.LineNumber ,t.SequenceInLine

go

Is it possible to enrich this store procedure to provided:

1. how long does this cmd exec

2. If this cmd is exec in a store procedure, how to show the procedure's name

Regards.

[former_member182259 · Accepted solution]

#2 is the easiest, so I will answer that first:  object_name(object_id [, dbid]).....you have both components in monProcessStatement (ProcedureID, DBID)

#1 is a bit tougher.   If all you need is wall clock time, dbisql will give you that in the messages window - and isql has an option to do so as well (isql -p ....I believe...ASE utility guide in doc set will say for sure).   Otherwise you are confusing a few concepts:

1 - monProcessStatement contains the *currently* executing statement - which, for a stored procedure would be the currently executing line number.   The StartTime for that current statement is in monProcessStatement.  As soon as it finishes, the data is flushed to monSysStatement (the historical pipe variant).   If you want the entire procedure execution time, you would have to sum the executions from monSysStatement for that SPID, KPID, DBID, ProcedureID, Context....and you would have to be quick due to pipe size....depending on activity.  If the proc hasn't finished executing, you might find some interest stats in monProcessProcedures

2 - In 15.7, you can get aggregated stats for each PLAN of a proc from monCachedProcedures.  Note that it will have ExecutionTime and ExecutionCount - so no way to tell what a single execution takes - but you can get an average

You might want to consider not joining with sysprocesses or even doing joins at all.   First of all, if I call the proc via an RPC call, there is no SQLText.....sooo ....you might miss data you are after.  Second of all, unlike monProcess, sysprocesses doesn't support arguments - therefore it is a full scan of ALL the pss structures - which on a system with a lot of users can take a bit - and since it is all in memory, it is a bunch of CPU. 

[former_member89972 · Accepted solution]

Hi

I have implemented a small variation of this. I collect the monProcessStatement every minute using plain bcp out. Loaded on a different ASE server is also a copy of sysobjects for the main database where I can look up procedure name using non zero value in ProcedureId column.  For approximate run time of a procedure I take minimum of start time and max of timestamp of collection. In most cases this serves us well.

HTH

Avinash