This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Blocked Sessions Graph

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?

pastedImage_0.png

  • 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,

    David

  • Glad you asked! emoticons_happy.png 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.

    David

  • I will take a look at these features.  Thank you very much!

    David

  • 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.

  • Okay.  This helps a lot.  Thank you very much.

    David

  • One other thing, is it possible to retain more than 1 month of blocking data in DPA?

    David

  • The blocking data is built from the detailed active session poll (we call it the quick poll). You can't keep more blocking data without keeping all of the detailed history. Having said that, yes, you can go to Options -> Administration Tab -> Advanced Options -> DB Instance Options tab -> make sure support options is checked and the instance is selected you want to make the change for.

    Scroll down or search for CLEAN_DAYS_OF_DETAIL - it should be set to the default of 30 days. This is where you can change how long to keep detailed data.

    Keep in mind though that about 90% of our storage usage for the repo is due to the detailed data. So keeping detail longer will cause the repo to "bloat" a bit. It may take a bit longer for some charts to render if it's building them from the detail data...