Troubleshooting Blocking Issues

  • Hi,

    Facing blocking issue in my databases. It is easy for me to find information about the blocking queries. For example, I can tell that one specific query waited 1339032.907 MS while the work time is 11.193 MS. I know this query is being blocked by another.

    The challenged is to find the blocker. Is there a way to find the time spent by a query as the blocker ?

    I have configured Extended Events, I can see the original blocker, but still yet not sure. Never know if it is part of a batch or not.

    Thank you

  • I'm not sure if there is a way to see how long a query was blocking other's (as opposed to being blocked, which is tracked), at least historically.

    My go-to tool for real-time blocking investigation is sp_whoisactive.

    You could also check out the Blocked Process Report. Do be careful though - if you set the frequency too low it WILL have an effect on the system's overall performance.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Hi,
    Brent Ozar had a very good blog about who_is_active. With this blog you are able to store informations about who is active in a table, and run the script x times with x seconds waits.
    https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/
    Just take a look, and try out.
    Kind regards,
    Andreas

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

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