Sybase MDA

Will there be any impact in Sybase performance in installing MDA tables
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nothing.  Installing the tables adds absolutely no overhead, at least until you actually set configuration parameters to turn on various data gathering options.

Using the MDA tables can be turned off and on one section at a time.  If you are having a problem with concurrency, you can turn on one set of statistics or a different set if you have a couple of slow running queries you are trying to find.

In general, the overhead of MDA is pretty trivial, usually on the order of a couple percent CPU.  It has been cleaned up somewhat in v15 whereas there were occasional issues in 12.5.x   Some operations are more expensive than others.  For instance, gathering all the SQL Text in a system that does a boat load of Dynamic SQL incurs more overhead than looking at say lock statistics.

Another issue is how you are going to use and/or capture the results.  The MDA data is generally stored internally in a circular buffer of a preset size, often configurable.  When you run out of buffer space, it is reused so you only get the most recent data, say SQL Text.  If you are really interested in capturing a bunch of this stuff, you have to have a process that regularly pulls the data and saves it to a database someplace.  One technique for doing this is to create a small database with tables that mirror the MDA tables.  You can do that easily with SELECT * INTO STATS_DB..PERM_<mda table>FROM <mda table>.  The create a stored procedure that loops through every so many minutes and does an INSERT/SELECT for each mda table you are trying to collect on.

BTW, there are a couple really good white papers on MDA usage on the Sybase website and some more in the ISUG resource library.

I've never had a chance to test the performance impact of MDA tables.   However, The Sybase TechWave 2005 Powerproint presentation on montables titled, "ASE MDA Tables In-Depth" which can be found at:

on page 33, says the following:

> Most non-pipes will not have significant impact. Some that do:
>   Statement/Per Object/SQL Text statistics & pipe (5-12%)
>   SQL Plan & Pipe (22%)

> Enable object/statement statistics periodically and collect information
> for analysis/profiling of the application

But it doesn't say what the "percent" is referring to.  Increased mem
usage? CPU usage?   I'm assuming it means either percent slower, or
percent increase CPU usage.

So if you want to minimize this undefined overhead, you *might* want to
turn off the following:

> sql text pipe active
> plan text pipe active
> statement pipe active
> errorlog pipe active
> deadlock pipe active
> statement statistics active
> per object statistics active

except for when you need them to debug a specific problem.

Ben Slade
Chevy Chase, MD

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Supposedly the pipe collection types were made much more efficient in v15 than in 12.5.x.  That doesn't do Prakash any good since he indicated v12.5 but it does bode well for using MDA going forward.

The overhead is substantial, 5% to 22% as published, and there are more than one TechWave papers.  On a server with a few CPUs, you will notice the overhead; on a server with many CPUs, you will probbly not.

The trick is to use them sparingly, turn on the config parms ONLY as you need them.

The next two issues you will come up against are:

1  MDA tables are transient.  They contain either snapshots (so you better be storing the snapshots if you want to compare them) or very transient info (so you better be grabbing snapshots very fast AND storing them if you want a history).  The point is, you need to:
a  set up your own permanent tables
b  set up a collection mechanism
c  and the admin requirements for that

2  I divide MDA into MDA/Statistics and MDA/Diagnostic.  MDA/Statistics is quite good, and I have [1] set up quite nicely with a mature set of scripts, while accepting the 5% overhead.  However, there are alternatives, sp_sysmon, and 3rd party monitoring tools: Bradmark Surveillance; Performance Centre; etc.

MDA/Diagnostics, if your purpose is to diagnose SQL problems and rogue queries, is hopeless, or requires masses of both machine resources and your admin code/time.  Sybase Auditing is by far the "best" way to capture the relevant info which is then used for several (not one) purposes, but it is seldom set up correctly (or you need to capture "everything" because stored procs are not used) and it does have 3-5% overhead.  There are much better alternatives in 3rd party tools: DS Auditor collects TDS network packets and is therefore zero overhead.  Either RapidSQL (with its Debugger) or DBOptimiser has substantial diagnostic capability.  After having a fair amount of experience with MDA/Diagnostics in my own code, as well as attending cust sites where I have no choice, I do not use it.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.