SQL Server Performance

STORED PROCEDURE ANALYSIS

Discussion in 'T-SQL Performance Tuning for Developers' started by HHAAPPYY, Jan 20, 2005.

  1. HHAAPPYY New Member

    CAN ANY BODY HELP ME ,
    I AM LOOKING FOR ANALYSING AN COMPLEX STORED PROCEDURE, I WANT TO KNOW HOW THE QUERIES BEHAVE IN THE SP .HOW MUCH TIME TIME AND HOW MANY READS AND WRITE TAKEN PLACE FOR EACH TEMP TABLE WHIHC I USED IN THE SP. WHAT THE DURATION OF TIME SPENT IN TEMP TABLE .

    I TRIED PROFILER , BUT NOT ABLE TO GET IT



    I NEED IT VERY UGRENTLY


    ALWAYS HAPPYNEES
    HHAAPPYY
  2. satya Moderator

    What kind of events you've captured using PROFILER?
    On the query analyzer you can paste the SP and check the execution plan about the process.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. HHAAPPYY New Member

    hi satya
    thanks for u r reply
    i used both these
    SQLProfilerTSQL_Duration and SQLProfilerStandard


    how i can check

    On the query analyzer you can paste the SP and check the execution plan about the process

    thnaks

    hhaappyy
  4. HHAAPPYY New Member

    hey i used

    session-------> existing connection
    stored procedure -----> rpc complete
    TSQL--> BatchComplete

    and datacolumns are
    event class
    textdata
    applicationname
    ntusername
    loginname
    cpu
    reads
    write
    duration
    startname
    endname
    nested level

    and the the standard
    SQLProfilerTSQL_Duration and SQLProfilerStandard from sql server


    thanks
    HHAAPPYY
  5. Luis Martin Moderator

    I suggest to include:<br />Store Procedures<br />SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted<br /><br />TSQL<br />SQL<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  6. HHAAPPYY New Member

    thanks Luis

    i did that also

    is there any way to find what I am looking for

    1.HOW QUERIES BEHAVE IN THE SP?
    2.HOW MUCH TIME TIME AND HOW MANY READS AND WRITE TAKEN PLACE FOR EACH TEMP TABLEs WHICH I USED IN THE SP?
    3.WHAT THE DURATION OF TIME SPENT IN TEMP TABLEs?


    thanks

    HHAAPPYY
  7. HHAAPPYY New Member

    in addition to it i would like to know is it good practice to call user's defined function int he stored procedure?

    thanks
    HAPPY
  8. Raulie New Member

    like mentioned above paste the Stored Procedure in the Query Analyzer Window and hit Control+L this will display the estimated execution plan in a graphical format. Also you can use this SET command.

    SET SHOWPLAN_ALL ON

    SQL Statement.........

    This will also give you detailed info on the SP.

    I would recommend staying away from UDF's if you can, they are known to be resource intensive.

    Raulie
    HP
  9. Luis Martin Moderator

    Step by step.

    1) Is no easy because when you see a query, in profiler, if that query is the only one (suppose that) when finish you have to see RPC completed belong to that query. But may be there is a lot of queries belongs to other stores procedures and is hard to know to witch one belong to one sp.

    2) Since there #aux tables in sp, those tables are written to tempdb. You can filter temdb database using database id. Also is good practice to improve performance, aislate that query and paste to Query Analyzer to see executio plan. Example:

    Some sp....

    insert into #auxtable select ............

    Copy and paste from select to end to see execution plan and find indexs if any.

    3) Same 2) about filtering by temdb id.

    There is a good freeware tool: Spotlight from Quest. You can see a lot with that tool, also sp in cache.

    HTH


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  10. Raulie New Member

  11. HHAAPPYY New Member

    thanks guys for your tips
    If i need any furthur infomation i will call you here only
    thanks
    HHAAPPYY

Share This Page