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.
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....
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.
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?
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.
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.
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.
