SQL Server Performance

Improving SP Performance

Discussion in 'T-SQL Performance Tuning for Developers' started by NileshRoy, Nov 27, 2007.

  1. NileshRoy New Member

    I need to improve performance of an SP. This SP calls another SP which is consuming 80% of time. I collected this info by inserting INSERT statement before call to inner SP. Both the outer and inner SP's are using CURSORS.
    Problem: I can not change the code of inner SP.
    Questions:
    Was my method of collecting processing time by inserting INSERT statement correct? Any better idea? Is there a way to find CPU cycles of both outer and inner SP.
    I have tried INDEX defragmentation by DBCC REINDEX, but this has not improved performance by 5%. Any other suggestions?
  2. ranjitjain New Member

    Hi,
    As you said, inner query uses cursors which should be avoided at all cost but you can not modify this sp. So I would say, you better write your own SP.
    Else in the current scenario, look at the execution plan, take advice from Index tuning advisor.
  3. NileshRoy New Member

    Is the method of collecting time info by inserting INSERT statement before and after the SP correct? Any better ideas?
  4. ranjitjain New Member

    Hi,
    From debugging purpose it's fine, else best is to run a profiler and get the actual duration for every statement in SP. Also if you can troubleshoot the same sp on test machine, check the actual execution plan along with server statistics. Also issue SET statistics io on to get the details of tables scan/reads. Also run update statistics on the whole database manually through job if huge insert/updates takes place on the tables.

Share This Page