August 20, 2013 at 10:01 pm
Hi All,
I have One stored Procedure having 1000 lines.In That Stored Procedure I have more than 500 queries.
Now i want to find out which query is taking more time with out using Sql Profiler?Is it Possible?
please folks share your comments
August 20, 2013 at 11:50 pm
have you checked the execution plan?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 21, 2013 at 12:44 am
The execution plan would be a good start, but I can imagine that this would be difficult to read with such a large stored proc.
But why don't you want to use profiler? Just setup a server side tracing with a filter for this stored procedure and the stements within. Run profiler for just the time this stored procedure is being executed (or executed multiple times to get better analysis on the results). You could even do this on a testing environment.
August 21, 2013 at 9:37 am
Maybe now would be a good time to pick up on Extended Events 🙂
August 22, 2013 at 4:44 pm
Do your own logging. Define a permanent table to log the date/time start and end of select statement in the stored procedure. You don't have to log after every select statement. Binary chop through the routine until you find the select taking all the time. Add one log point in the middle, to find if the problem is in the first or second half. Then add another log point at the quarter point. Repeat 9 or 10 times, and you should get a result.
August 22, 2013 at 10:10 pm
Thanks for spending valuable time and suggestions......
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply