Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Sybase monProcessActivity

Posted on 2011-03-22
Medium Priority
Last Modified: 2012-05-11
Hello All,

I've noticed in 12.5 monProcessActivity (MDA Table), there is no timestamp column.  Anybody know how offen monProcessActivity retain its data? Does it get refresh?  I'm trying to collect total number of committed transaction in a 24 hours period without using sp_sysmon.

Thank you,
Question by:tranicus
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 35197224
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.

Author Comment

ID: 35197762
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.  
LVL 24

Accepted Solution

Joe Woodhouse earned 2000 total points
ID: 35197875
sp_sysmon doesn't use MDA tables, but it is using some of the same underlying infrastructure.

You may find what you want in the history tables rather than the session tables, ie the monSys% MDA tables.

Like the monProcess% tables, they are refreshed each time they are accessed... but they contain a cumulative history of everything that's happened since the last time it was accessed or since ASE was booted if they haven't been accessed yet.

sp_sysmon is doing something similar. It inspects a bunch of monitor counters at the start of its sample interval, and again at the end, and calculates a number of deltas or changes between the start and end numbers.

monProcessActivity can't give you what you say you want, because it only knows about what's running right then. It doesn't aggregate over time, and has no history, it only shows a snapshot in time. This is useless for what you want.

In fact I've just gone and reread all my MDA docs and presentations from Techwave and I don't think MDA can give you this number (total committed xacts per day).

Is there a reason you don't want to use sp_sysmon? It would do the job - run it every 20 minutes, grep for the committed xacts, sum them.

If you really don't want to or can't use sp_sysmon, the Monitor Server and Historical Server can do this for you. They still exist and still work, but there is a bit of a learning curve to using them, and they interfere with using sp_sysmon (and to some extent the MDA tables too).

Take a step backwards. Why do you want to know the number of committed xacts per day? What will knowing that allow you to do? We may be able to get at it from another direction.

Author Comment

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

Expert Comment

by:Joe Woodhouse
ID: 35197961
Glad that helped!

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

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With the evolution of technology, we have finally reached a point where it is possible to have home automation features like having your thermostat turn up and door lock itself when you leave, as well as a complete home security system. This is a st…
Geo-targeting is the practice of distributing content based on a person’s location, as best as you can determine it. Let’s look at some ways you could successfully use this tactic. The following tips and case studies could lead to meaningful results.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question