on 06-04-2014 1:24 PM
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.
#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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
61 | |
9 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.