Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    167

    Unanswered: How do you check the performance of a stored procedure?

    I have an interview and I'm always asked in interviews the following question(s)....

    - "How do you check the performance of a stored procedure?"

    - "How do you check if a specific stored procedure is running long?"

    I have no idea! I'm hoping someone can help point me in the right direction when it comes to viewing or monitoring a stored procedures performance.

    For example... are there any system stored procedures or system tables that I can run or look at that will give me the overall status for a stored procedure?

    Or is this something that can only be done through 3rd part tools (i.e. ProActive DBA)?

    Thank you
    Last edited by ftmjr; 08-06-07 at 23:53. Reason: Are you kidding?

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Can you tell me where this interview is please? I know the answers to them.

    The easiest way to track a stored procedure performance is via MDA. However, to be effective you need to build an infrastructure around MDA to capture proc runs and then reports.

    There are several reports you can generate from the captured output. How long the proc took today as opposed to yesterday or a month ago. Compare this against any increase in data in the underlying tables, then check if there is any increase in the physical io. If the physical io has shotup lately by multiple of amounts, that would mean the underlying tables need update stat'ing or the proc is not picking up a good index.

    You could also tell the exact line number on which the proc waited on the most, extract the proc and tune the SQL.


    ProActive DBA also can provide all these in a nice report as part of their SQL Capture filter.

  3. #3
    Join Date
    Aug 2007
    Posts
    59

    Red face

    Hi

    What is this MDA?
    is it any external tool? if it is where can i get?
    if not can you pl tell the details how to check the performance of stored procedure?

  4. #4
    Join Date
    Mar 2007
    Posts
    167

    MDA monitoring tables...

    MDA is also known as "monitoring tables". They are internal to Sybase ASE. They do have to be installed separately, after your ASE/dataserver install.

    Please refer to these notes...
    ===============================================
    The monitoring tables are not created by default, but must be created using the installmontables script. See “Installing the monitoring tables” for more information.

    You must have the mon_role role to query these tables. For more information, see “The mon_role role”.
    ===============================================

    Please refer to the Sybase documentation below (Performance and Tuning: Monitoring and Analyzing):

    http://infocenter.sybase.com/help/in...ring/title.htm

    I hope this information is helpful.

  5. #5
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Willy is on the money MDA can be god sent at times.

    You should also read up on

    a) showplan and how to interpret it
    - join order
    - dol vs all pages and how its structured
    - types of update / delete modes in all pages / dol
    b) statistics io
    - selectivity and density.
    - physical vs cache reads
    c) use of flags 302/307/310 to see how the optimizer decides on a particular show plan.

    Some common problems with Stored Procedures and related topics. Its almost impossible for me to explain in full and some of it may be a bit advanced... But just to give some hints and areas to read and if you do get this mastered, you will find no issues in interviews

    a) Define / Use of variables within stored procedure
    b) Use of uncommon values in parameter values on the first execution after a stored proc is created/recompiled, resulting in a bad query plan chosen by the optimizer.
    c) Join order. Whats an inner and outer table. What is nested join and/or merge join. And understand how the optimizer chooses the join order.
    d) Lack of statistics on a non-leading column in a composite index resulting in optimiser choosing a table scan as opposed to an index scan.
    e) Use of like with %%. The optimizer will chose an index if you can afford a like with only a trailing %. i.e. like 'Wa%' is much better than '%Wa%'
    f) Creation of tables within the same stored proc and using it and the optimizer has no way to find best access method for the table.
    g) Joins vs subqueries. Usually updates/deletes benefit from subqueries.
    h) Avoid self joins if possible.
    i) Use "NOT NULL" where possible in table definition. It will help the optimizer.
    j) Use fixed length instead of variable length for small length fields. Read about how DOL/Allpages change data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •