Make Blockers a "first class" issue like Waits

I understand that DPA was founded on the premise of helping to identify and reduce waits. However, we consider blocking to be at least as significant. If a user runs a procedure and it takes 20 seconds and that isn't blocking anyone else, we consider that less of a problem than when a user runs a procedure that takes 10 seconds and blocks 30 other sessions for much of that time, we consider that to be much more of a priority to resolve than the single-user issue.

To help with that:

The existing Blockers tab could be renamed Blocking Detail, and ideally would be available for time frames longer than one hour.
A tab for Blocking that is similar to the tab for Trends could be made available in the All Days and single date views as well as the hourly (and smaller time frame) views. It could feature a chart showing which statements caused the most seconds of blocking.
On the SQL tab, the current Advisor tab could be renamed Wait Advisors and a similar tab named Blocking Advisors could be added ("Query 5557571535 accounted for 12% of instance blocking time, plan indicates a full table scan. more...")

Of course, this assumes that the blockers are what I am interested in most. In general, optimizing those statements is of significant benefit. But if they have already been optimized, I then become more interested in what is getting blocked. For example, if a SELECT is experiencing significant time being blocked, perhaps it would benefit from a SET TRANSACTION ISOLATION LEVEL statement of some type. Any help DPA can be in identifying such situations would be very helpful.

Find more posts tagged with

Blocking

blocker

sql_blocking

blocked

Status: None

Comments

brianflynn

I'd be interested to discuss and white board ideas with you if you are up to it. It seems to me that simply having the ability to filter the SQL tab under TRENDS for only blocking wait types might go a long way here. Does that make sense to you? Because anytime blocking is occurring, you are accumulating blocking wait types for the victims of the blocks. These waits may be minuscule with respect to your other waits and thus drowned out of the SQL TRENDS view. Does that sound right to you?

m60freeman

I agree that being able to filter the Trends view to show only blocking waits would be a step forward. It should affect all the tabs under Trends, and also affect the Advisors. I want to deal with statements that consume too much CPU or memory, but only after I deal with the ones that are keeping dozens of users from updating records while they execute.

Not to get too far off track, but I'd definitely benefit from being able to filter in and out specific waits, possibly by being able to build my own custom wait collections which I could then select as filters on the Trends tab. For example, in addition to a "Blocking Waits Only" filter, I could also benefit tremendously from an "All But ASYNC_NETWORK_IO" filter to get rid of the noise from our Microsoft Access application. That noise obscures almost all useful information. Four of the Advisor hints (and 10 of the top 15 SQL) every day on my main production server are about these statements that I cannot do anything about. (Meanwhile, the users of the Access application are generally not noticing any performance issue despite the charts showing tens of thousands of seconds of these ASYNC_NETWORK_IO waits every hour.) Note here that the LCK_* waits start at #16 on the list and are invisible in the chart bars:

Participating in a white boarding session would be a significant hassle for me, as I have no headset or company phone and work in an open office.

m60freeman

I received this email alert at 9:08:

Alert: Max Blocking Time Per Session - custom
Database Instance: S-SW-PRODSQL2
Execution Time: Wednesday - July 29, 2015 09:07:53
View Alert Status: http://S-SW-NetMan:8123/iwc/alertMain.iwc
Alert Parameters:
Description: This alert will calculate the maximum blocking time per session. It divides the total amount of blocking time by the number of sessions being blocked. If there are 20 sessions blocked for 600 seconds, this alert would show a 30 second average.
This instance just had a blocking problem where the maximum blocking time per session is below.
Value: 67 Maximum Blocking Time (secs)
This message was system-generated. Do not reply to this message

At 9:09am, I pull up DPA and under Trends, it won't show anything past 8 - 9am. I look under Current, and it doesn't show any current blocking. I have to wait until 9:10am before I can see the detail of this blocking issue in Trends.

I realize that this is a bigger issue that just blocking (Trends needs to show any data already collected, even if it is within the past 10 minutes), but Blocking is what caused me to become more painfully aware of this gap.