Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Procedure Having More than 1000 Lines Expand / Collapse
Author
Message
Posted Tuesday, August 20, 2013 10:01 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, May 21, 2015 1:02 AM
Points: 691, Visits: 840
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
Post #1486500
Posted Tuesday, August 20, 2013 11:50 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, May 21, 2015 12:59 AM
Points: 2,030, Visits: 2,535
have you checked the execution plan?


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1486526
Posted Wednesday, August 21, 2013 12:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:18 PM
Points: 2,596, Visits: 3,108
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.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1486550
Posted Wednesday, August 21, 2013 9:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
Maybe now would be a good time to pick up on Extended Events
Post #1486777
Posted Thursday, August 22, 2013 4:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 8, 2015 10:29 PM
Points: 26, Visits: 163
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.
Post #1487580
Posted Thursday, August 22, 2013 10:10 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, May 21, 2015 1:02 AM
Points: 691, Visits: 840
Thanks for spending valuable time and suggestions......
Post #1487629
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse