how to troubleshoot slow running stored proc

  • Hi All,

    Assuming I have an problematic stored procedure in production which is taking 45 mins to execute, I want to trace or track which stmt is taking up more resources I/O,cpu,memory,or getting blocked and so long running(duration).

    Assume that within my stored proc I have 10 sql stmts and wanted to track down those 2-3 statements causing the slowness.(may be blocking . may be reading a whole of data , long running, high reads,high cpu or something else ..)

    I also, want to track down compile time and run time parameter values to find out if there is any issue with parameters being passed or wrong plan is being picked up.  Now suppose, I want to setup some kind of trace to gather information , in such case, what columns do I need to choose while setting a trace for troubleshooting a specific stored procedure by putting a filter on dbname and stored proc name. Second part is, after collecting the trace , how to do the analysis (reporting query) whether we need to check by total duration or reads or cpu time?  Can anyone provide your inputs on how to do multi-dimensional analysis and why to do that slicing dicing ? Please provide some ideas.

    If anyone has already implemented such trace, it would be a great help if it can be shared. I am using SQL 2016 EE.

    Thanks,

    Bob

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Can't say anything without seeing the code

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Checking on what columns should be of interest if we create some sort of trace.

  • Run it manually once with the execution plan turned on.

    Also, look into Brent Ozar's "First Responder Kit".  It's free and the code is open for you to use to learn from and modify if needed.

    The other thing to remember is that your longest running queries are usually not your worst queries.  It's usually the things that are executed thousands of times per hour.  Brent's tools will help you there, as well.

    Of course, you could go to the Object Explorer, right click on the instance, select reports, and the follow your nose in the performance reports that will show you the worst resource consuming queries  that are in cache.  These may actually be a part of a stored procedure which helps with your other question about how to find the parts of stored procedures that are the worst.  Brent's code, Adam Machanic's sp_WhoIsActive, and most monitoring tools do the same thing except you can usually see the code they're using and capture the results over time.

    Another tool that comes with SQL Server is "Query Store".  I've not used it and probably won't but other's swear by it.

    --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)

  • Thanks Jeff for those pointers. I am basically checking for an extended event trace definition file to track these numbers.

    I never used sp_blitz and not sure how it will behave on prod.

    Looking for a simple straight forward trace or extended event trace definition file where I can start the trace and stop it manually and executing a reporting query for analysis based on different columns.

    The other thing I was looking for is analysis part.. sample queries which can used for slice and dicing analysis. ( like by reads, by cpu etc...)

     

  • This was removed by the editor as SPAM

  • bobrooney.81 wrote:

    Thanks Jeff for those pointers. I am basically checking for an extended event trace definition file to track these numbers.

    I never used sp_blitz and not sure how it will behave on prod.

    Looking for a simple straight forward trace or extended event trace definition file where I can start the trace and stop it manually and executing a reporting query for analysis based on different columns.

    The other thing I was looking for is analysis part.. sample queries which can used for slice and dicing analysis. ( like by reads, by cpu etc...)

    That's why I recommend Brent Ozar's stuff.  It does a whole lot of that.

    --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)

  • Focus first on logical I/Os, as that's usually the culprit one way or another.

    If you can run the code at will, then add this statement at the start of the proc/code:

    SET STATISTICS IO ON;

    Then run the code yourself.  See which statement(s) are using the most logical I/Os and review those statements.

     

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • I would take all the queries out of the stored procedure and run them individually in SSMS with SET STATISTICS IO, TIME ON and also showing the execution plan. It should then become pretty obvious which queries within the SP are causing the slow runtime.

     

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • garkbeda43 wrote:

    Focus first on logical I/Os, as that's usually the culprit one way or another.

    You just re-stated exactly the first line of what I wrote in an earlier post.  What is the point of that?

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher wrote:

    garkbeda43 wrote:

    Focus first on logical I/Os, as that's usually the culprit one way or another.

    You just re-stated exactly the first line of what I wrote in an earlier post.  What is the point of that?

    its a spammer

  • frederico_fonseca wrote:

    ScottPletcher wrote:

    garkbeda43 wrote:

    Focus first on logical I/Os, as that's usually the culprit one way or another.

    You just re-stated exactly the first line of what I wrote in an earlier post.  What is the point of that?

    its a spammer

    Usually a SPAM test to see if a connection worked and who will notice.  For these kinds in particular, it's a bot.

    --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 15 posts - 1 through 14 (of 14 total)

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