Using PRINT for time usage in long SP (is there another way?)

Question

Using PRINT for time usage in long SP (is there another way?)

Carl Uman

SSCommitted

Points: 1993
More actions
July 7, 2006 at 8:15 am

#114165

Seems like a basic question. We had someone leave on vacation this week and I'm working on a long running procedure they wrote (that shouldn't be). I'm trying to find what part of the SP is taking so long so thought I'd put in some print statements with current time but they don't print in the messages until it SP is done (I'm running this in QA). Is there some way to force the print statements to show up in messages before the SP is complete or is there a better way of doing this? The SP is ~1200 lines with ~69 selects/inserts/updates so any help would be great. This and other long running SPs will be rewriten but I've got a user waiting on results for now.
Thanks a bunch
Carl

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Is this in a test environment? I would set up a SQL Profiler trace. Make sure you use the following events: SP:Starting, SP:Completed, SP:StmtStarting, SP:StmtCompleted, SQL:StmtStarting, SQL:StmtCompleted. Also, in the Columns tab of the trace setup, make sure you add EndTime. You can then use the StartTime/EndTime columns along with the SP:StmtCompleted, SQL:StmtCompeted events to get exact durations, reads, writes, etc. for each of your 69 statements.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 2

Could also built a log table (Proc Name, Line, Time) and insert rows in there as the procedure runs. Place inserts around sections or even lines and you can check that while the proc runs.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

In Query Analyzer, Tools, Options, "Results" tab, check "Scroll results as received".

Mark

Amin-231896 SSC Rookie Points: 27 More actions · Answer 4

You may use SELECT GETDATE() instead of print. After execution of SP, compare the difference between GETDATEs to get closer to the bottleneck!

Carl Uman SSCommitted Points: 1993 More actions · Answer 5

All great answers. I did find the bad code already but will test these ideas.

The symtom was 141,872 rows with a length of 12,433 bytes being sorted

The problem is that it shouldn't be getting 141,872 rows.