Post reply

how to find why the stored procedure took time to execute

  • August 7, 2020 at 8:00 am

    #3778097

    Hi,

    This is one of the incident at our clients side and does not happen regularly. A stored procedure(stp) which usually executes in few seconds took 10 mins to execute. i want to analyse why that stp took time to execute or which other factors were the cause of slow execution of the stp. we have got the db restored. which query should we execute to know the same.

    Regards,

    Saumik Vora

  • August 7, 2020 at 12:49 pm

    #3778162

    Nothing you do is going to show that. A newly restored database has no performance metrics stored with it of any kind.

    In order to diagnose this kind of behavior, you have to set up a capture of some kind ahead of when that behavior occurs. Assuming you're in 2008 since we're on the 2008 forum, your only choice is to set up a server side trace on the client's machine and then consume that data. If you were on 2012 or better you could use Extended Events, which are much more lightweight and offer better filtering. If you were on 2016 or better you could add in Query Store, but it's not terribly granular, so you might not see why the query ran long.

    I'd also suggest getting a blocked process report set up. You could be looking at one of two situations. One, bad parameter sniffing is causing poor performance until a recompile resolves it. Two, some process is blocking the procedure, making it run long occasionally.

    However, until you do these things, there's literally no way to know from where you're currently sitting. Sorry.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 7, 2020 at 1:32 pm

    #3778186

    This is one of the reasons why I use RAISERROR('Section: %s Duration(ms): %u',0,0,@SectionName,@Duration) WITH NOWAIT: in virtually ever section of stored procedures I write to write section name, duration and, sometimes, rowcount to the output of the job or to the screen depending on what's running it.  Depending on the proc, I might also use multiple RAISERRORs to list the input variables (if any) so I can do a test with the variables that caused the run to go haywire.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

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