Hello, we are seeing these results in our environment and are wondering exactly what this means? For example, does it mean we had 2 sessions blocked for an entire day?
We run that every 60 seconds, so basically, we have to catch it in the act at the moment of polling.
Our quick poll runs once a second and will pick up much better the impacts of blocking in your environment.
I'm not going to say don't pay too much attention to that metric, but the blocking correlation tab and blocking tree tab are the things to focus on.
We run a query against the monitored instance once per minute. We look for any blocked sessions. If we see any, we plot them (per the graph).
Since I see Batch Requests/Sec, I'll assume this is a SQL Server instance (correct me if I'm not right about that).
I've included the query below.
When you zoom out, I believe the behavior is to aggregate/summarize the blocked sessions.
So what you are seeing is all of the blocked sessions observed in that day.
Those blocks could have occurred for seconds or minutes or ...? Unknown until you dig more into this.
What version of DPA are you on? Reason I ask is that we introduced a really great feature in 10.2 for blocking and deadlocks for SQL.
SELECT COUNT(1) FROM master..sysprocesses AS s
WHERE s.status<>'background'
AND s.cmd<>'AWAITING COMMAND'
AND s.blocked>0
Thank you for your response. Yes it is SQL Server and we are on DPA version 11.0.387. Still not clear on the significance of this graph. Does it mean it only captured 2 blocks for the entire day? Each one of those bars is a day.
David
Also, is there a way to drill into this data to see exactly what caused the blocking?
Thank you,
Glad you asked! Yes there is.
First of all, this is just a count of *observed* blocked sessions for any given period. Since you are looking at a long timeframe, it's a summary of the counts of observed blocked sessions for a day. If you were to zoom in to 24 hours or something like that, you may still see counts, but it would be for whatever timeslice is represented for that data point.
On to a better way... Since you are on a later release of DPA, on the trend view, you will see a blockers tab at the bottom that correlates to the wait trend above. That correlated blocking tab has two views within it (root blocking SQL or culprits and top waiting SQL or victims). Make sure to note the scale on the left for both the trend chart (BTW, when looking at the correlated tabs, I like to view the Waits tab in the upper chart - better representation of total wait time in the instance vs. top 15 SQL) and the blocking chart as they may be different. This will give you a good idea overall how much impact blocking has in your instance. If it's minimal, then I wouldn't expend a lot of energy digging into it as end-users are likely to not be impacted by it (not suffering). If it is significant, look at the blocking SQL, see if you can tune it. Look at the way the app is multithreaded and perhaps even the data model for hot objects from a blocking perspective (remember, it could be an index at the core of the blocking contention). This is the fun DBA stuff IMO.
Also, within an hour, on the trend graph, you will see a blockers tab. You can get the full session blocking tree including SQL involved from that to help full out the picture even more.
Hope that helps...
If I run that SQL command you provided over an over I see momentary blocks of varying amounts so I am not sure what this graph means.
Okay. This helps a lot. Thank you very much.
One other thing, is it possible to retain more than 1 month of blocking data in DPA?
