Help with detecting and sending alerts for lengthy blocking
-
Over the weekend we had 2 ETL jobs that were causing unresolved blocking with each other. One reading data the other inserting and updating. The jobs had been running for 45 hours in the blocked state. This was only resolved after one of the processes was killed.
I'm looking for some help with the best technique to detect and alert for any blocking that goes on for an extended period.
I was thinking to just run a job every 15 minutes looking at DMVs for blocking and send an alert for any transaction that goes over xx minutes.
We may want to have different thresholds depending on which database is involved.
Not looking for a solution just some suggestions for methods that have worked well for others.
thanks
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
- TangoVictor wrote:
Not looking for a solution just some suggestions for methods that have worked well for others.
You can actually setup an alert for blocking and then you can have that alert call a stored procedure to get some amplifying information, record that information in a table for posterity and troubleshooting purposes, send an email, hit some pages, and do some screen-pops for people that need to be made aware (this is what I did).
There's no need to setup a special checking job like the one you suggest but that would work, as well.
--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) -
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
This was removed by the editor as SPAM
- Jeff Moden wrote:
You can actually setup an alert for blocking and then you can have that alert call a stored procedure to get some amplifying information, record that information in a table for posterity and troubleshooting purposes, send an email, hit some pages, and do some screen-pops for people that need to be made aware (this is what I did).
There's no need to setup a special checking job like the one you suggest but that would work, as well.
Jeff are you talking about an agent alert? Like the attached?
- TangoVictor wrote:Jeff Moden wrote:
You can actually setup an alert for blocking and then you can have that alert call a stored procedure to get some amplifying information, record that information in a table for posterity and troubleshooting purposes, send an email, hit some pages, and do some screen-pops for people that need to be made aware (this is what I did).
There's no need to setup a special checking job like the one you suggest but that would work, as well.
Jeff are you talking about an agent alert? Like the attached?
Yes... an Agent Alert. No... not like your attached. Like this... note the object and the counter names. You may have to change "Alert if counter" settings to "rises above" (like I did) and perhaps change your value to "0" instead of "1". Make adjustments from there. I set mine to "1" just because I know we have certain things that cause regular short term blocking and the typically only only block one other process. I also have a stored procedure in a job that gets executed if the alert fires and that stored procedure captures a bunch of stuff for me which also frequently contains the offending code in the blocker(s) and the code being blocked.
--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) -
All the votes for Johan's solution with Extended Events. #TeamXE
Also, I unspammed TangoVictor's first post, not noticing the second. Not sure why that happened. I put it back to spam since the second one came through OK.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thank you Grant, actually if you have the power you can just delete that, I didn't redact parts of the screenshot I should have. Reporting it was the only I could find to remove it. Why is there not a delete?
The main thing I want is a notification, the XEvents are good for additional info. Jeff's solution using the agent and also capturing the xevents seems like the right combination, which was also noted in the link posted by Johan so thanks for the vote on that as well. - TV
- TangoVictor wrote:
Thank you Grant, actually if you have the power you can just delete that, I didn't redact parts of the screenshot I should have. Reporting it was the only I could find to remove it. Why is there not a delete?
Sorry. I don't see a way to delete it. I could delete the attachment (even though it's hidden) so that's gone.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant, that would be great, thank you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply