i have a procedure that do multiple calculation , insert, update , select data from tables, execute other procedures. My problen is that it is taking too much time to complete but i am not able to find which part of procedure is taking time. Is ther any way to get know the exact part of whole proc. I tried various method like profler and long rnning queries finding an activity monitor but that help nothing.

i have a procedure that do multiple calculation , insert, update , select data from tables, execute other procedures. My problen is that it is taking too much time to complete but i am not able to find which part of procedure is taking time. Is ther any way to get know the exact part of whole proc. I tried various method like profler and long rnning queries finding an activity monitor but that help nothing.

Kindly suggest me correct approach.

prithvi nath pandey

You can turn on "Query->Include Actual Execution Plan" option (Control-M), run the query and look at the execution plan. It will show you the relative costs of various parts in the stored procedure to help you narrow down where the bottlenecks are. Once you find those, you will get some clues.

Another possibility is to insert debug statements within the stored procedure that prints out the elapsed time, so you can zero in on the parts that are consuming most time.