[Webinar] Streamline your web hosting managementRegister Today


Sybase MDA

Posted on 2008-11-17
Medium Priority
Last Modified: 2012-05-05
Will there be any impact in Sybase performance in installing MDA tables
Question by:prakashbuz
  • 2
LVL 19

Expert Comment

ID: 22977717
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.


Accepted Solution

BenSlade earned 150 total points
ID: 22979970
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
LVL 19

Expert Comment

ID: 22980162

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.


Expert Comment

ID: 23038865
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.


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

After a recent Outlook migration from a 2007 to 2010 environment, some issues with Distribution List owners were realized. In this article, I explain how that was rectified.
If you have a smartphone, I'm sure you've noticed that web applications are changing. Yet, are you aware of how these changes are going to impact your organization or company?
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

591 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