January 11, 2017 at 3:26 pm
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
January 11, 2017 at 3:31 pm
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
January 13, 2017 at 2:11 am
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