Post reply

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

  • 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

  • July 7, 2006 at 8:57 am

    #648011

    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

  • July 7, 2006 at 9:18 am

    #648020

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 7, 2006 at 9:19 am

    #648022

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



    Mark

  • July 7, 2006 at 10:27 am

    #648036

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

  • July 7, 2006 at 10:38 am

    #648038

    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.

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

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