Sybase MDA

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

    Expert Comment

    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.

    LVL 1

    Accepted Solution

    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


    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.

    LVL 6

    Expert Comment

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Great sound, comfort and fit, excellent build quality, versatility, compatibility. These are just some of the many reasons for choosing a headset from Sennheiser.
    There are many benefits to finding online courses that align with your personal or career goals. Read more about our reasons for continuing your education in technology.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now