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
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:16 PM
Points: 437, Visits: 564
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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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: Today @ 2:25 AM
Points: 2,237, Visits: 2,686
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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:16 PM
Points: 24, Visits: 157
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
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:16 PM
Points: 437, Visits: 564
Thanks for spending valuable time and suggestions......
Post #1487629
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse