January 17, 2006 at 11:55 am
hi,
I'm running a procedure and I can only tell which part of the proc is running by view what files it is updating and by check for certain data values I expect to see. Apart from this guesswork is there are definitive command that tells me which command is actually running?
sp_who2 give a certain amount of information but not what I need.
Basically I think there's a bottleneck which I want to find.
Lastly......... its a 6.5 system
ANy help wlecome,
Eamon
January 17, 2006 at 12:00 pm
Can you use the sql profiler to trace your stored procedure?
January 17, 2006 at 12:29 pm
Or look at the execution plan for the proc...
January 17, 2006 at 1:00 pm
>> can only tell which part of the proc is running by view what files it is updating and by check for certain data values I expect to see
If you can see data changing as the proc runs, then it's not 1 efficient set-based operation within a transaction. Also, using the word "files" instead of tables ? I smell a cursor and a procedural language solution.
January 17, 2006 at 9:19 pm
Create a table with two columns. One will be where in the process the sp is the other is the time. Now go to your proc and throughout it add inserts that say what the sp is doing and getdate() to insert data into your new table. Now you can see where the sp is slow by looking at the data in your table.
This is a little brute force, but with 6.5 that is the road you have to take sometimes.
January 18, 2006 at 2:52 am
Recompile the stored procedure and see..if it works.
jyothi..
January 18, 2006 at 3:01 am
Thanks PW !!!
You're cursor suspicions were correct.
It was the bottle neck in the process as I rewrote the logic using several updates.
THANKS !!!!!!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply