SQL Server Performance

SP running slowly

Discussion in 'Performance Tuning for DBAs' started by jsv, Dec 26, 2007.

  1. jsv New Member

    Hi i am shashi. I am new to the DBA work. I am working for tuning of an SP. Till yesterday the SP ran for 2 hrs , but today it
    is taking 9hrs to run . Can anyone suggest how to start the tuning . Their are many DML statements written in SP. What the
    basic things should i look at . Their are around 8 to 10 tables inside the SP. Should i look at the statistics and Logical scan
    fragmentation. pls help me, Thanks in advance.
  2. Luis Martin Moderator

    Welcome to the forum.
    About problem:
    1) Check execution plan to find out if any index is necessary.
    2) Frequently run reindex and update statistics.
    3) using Query Analyzer run:
    set statistics io on
    your sp
    set statistics io off
    check if you have a lot of read ahead. I so, then you need some index.
    Just to start....
  3. ranjitjain New Member

    Hi,
    I think if data in those tables have been modified hugely, i.e. lots of bulk inserts/delete has taken place then db might be having outdated statistics. Run update statistics on the whole db and check all of statistics updated.
  4. satya Moderator

    I guess you need better understanding on the exeuction plans & indexing too, in this case take your time to review the articles section on this website that will get you more information on how best you can configure/fine tune your code for a performance gain.
    BTW< how often the reindexing and update statistics job runs on these tables?
  5. jsv New Member

    HI,
    Thanks for all yur replies . Actually the SP has to run only once per day , but some user was running sp already . For that reason it tuk
    a long time to execute . Anways i rebuild the indexes and updated the statistics. SP is running fine now.
    Thanks,
    Shashi.
  6. ndinakar Member

    DML's in procs can cause recompilations. why does the schema need to change every day is the bigger question? If you are using 2005 you can look into different hints like using an existing plan that you know is good.
  7. satya Moderator

    Can you seperate those DML statements from thsi SP, in order to reduce such a performance loss. As suggested you should base out the recompilation that are causing main issue even though base indexes are reindexed.

Share This Page