Post reply

Extended event - Capture blocking only when stored procedure is running?

  • September 24, 2019 at 4:14 pm

    #3682987

    Hi

    I was wondering if it is possible to capture blocking events only if a stored procedure is running and only when it has been running for 500 ms.

    My issue is that i have a stored procedure that runs quite well most of the time but once in a while the duration gets longer and longer and longer and then back to normal running. I'm suspecting blocking issues.

    Is it possible to capture blocking where the duration is from 500ms and up with extended events and only if the stored procedure is executing?Or does anybody have a better idea to finding out if blocking is an issue?

  • September 25, 2019 at 5:10 pm

    #3683380

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

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

  • September 25, 2019 at 8:02 pm

    #3683428

    The Blocked Process Threshold in SQL Server is measured in seconds, so the minimum would be 1 second.  You'd have to shred the XML in the Blocked Process Report to determine which sessions where involved and what was running.  With such a low threshold you could pick up quite a bit of noise while trying to find info about 1 specific procedure.

  • September 26, 2019 at 6:12 am

    #3683513

    Hi Chris

    Thanks, do you have another idea to capture blocking with such a low threshold?

    /Jacob

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

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