locked
Extended Event Session To Capture What Waits A Stored Proc Is Waiting On. RRS feed

  • Question

  • Hey guys, I'm trying to setup an extended events session to try to capture what particular waits a stored procedure is waiting on when it runs for longer than 5 seconds.

    Friday, January 1, 2016 10:04 PM

Answers

  • This is not exactly what you are looking for, but maybe something you can work from. Take a look at Lee Tudor's sp_sqltrace which is on my web site:
    http://www.sommarskog.se/sqlutil/sqltrace.html

    It sets up a trace filtered for a spid and also an X-events session for the same spid and captures which statements that takes most time and resources and what they are waiting for.

    Saturday, January 2, 2016 9:38 AM

All replies

  • Hello,

    The following article seems to have what you are looking for:

    http://johnsterrett.com/2013/10/10/what-queries-are-causing-my-waits/



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Saturday, January 2, 2016 2:48 AM
  • Thanks for the link but this is not exactly what I'm looking for.  What this script does is finds the sql text of a session with the highest wait types.  What I want is the opposite.  I want to find out is what waits my stored procedure is waiting for.
    Saturday, January 2, 2016 4:27 AM
  • HI,

    this may help your.

    https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-30-of-31-tracking-session-and-statement-level-waits/


    Please click Mark As Answer if my post helped.

    Saturday, January 2, 2016 5:18 AM
  • This is not exactly what you are looking for, but maybe something you can work from. Take a look at Lee Tudor's sp_sqltrace which is on my web site:
    http://www.sommarskog.se/sqlutil/sqltrace.html

    It sets up a trace filtered for a spid and also an X-events session for the same spid and captures which statements that takes most time and resources and what they are waiting for.

    Saturday, January 2, 2016 9:38 AM