Sybase monProcessActivity

The monProcess% tables are only a snapshot of the process activity at the time they are queried - exactly like querying sysprocesses (and in fact hooking into many of the same areas of the ASE kernel).

Thus monProcessActivity retains no data at all, under any circumstances. Each time you query it you get a new snapshot of the process activity at that moment.

Good practice with any MDA table is to copy it into a "real" table and work with that data. You might think you could therefore insert to some kind of transaction history table the current contents of monProcessActicity, possibly formatted/manipulated/aggregated, but this is not actually a good idea. The MDA tables are not real tables, they are stored procedures whose result set is exposed as a table, and you can get poor performance issues if you hold that result set open for any kind of join, aggregation or other processing. Also anything that requires accessing the MDA table more than once (like a subquery being run once for each qualifying outer row) will behave strangely as the MDA table will return different results each time it is run.

So best practice with MDA is to dump it, as is, with as little processing as possible (preferably no processing at all, not even a WHERE clause), straight into a temp table. Then you can go to work on it - filter it, aggregate it, index it, format it - and send those results to your more permanent archive table.

To answer your specific question, all monProcess% tables are refreshed every time they are accessed.

Hello Joe,

Thank you for the info but it still bug me. How does sp_sysmon able to pull those 'transaction summary, committed Xacts'?  One would think it have to come from somewhere.  I can't imagine an automated process run on hourly, dumping data from monProcessActiviry into a historical table.  

Much appreciated Joe... You gave me alot to think about the MDA tables, I wish Sybase were better in it explaination.

Glad that helped!

You and others might find the resources at http://www.sypron.nl/mda.html helpful. Good luck!