DPA 10.2 Now Available - Root Blockers, Idle Blockers and Deadlocks

Time flies when you were having fun, so I must be having a blast because Database Performance Analyzer 10.2​  has arrived in a blink of an eye (just 6 months since 10.1).  If you are ready, you can download it from the customer portal and for free evaluation from the SolarWinds Website. Be prepared to save hours every day as we take analysis of blocking and deadlocks to a whole new level.

New Features in 10.2

  • Identify Root Blockers causing the greatest impact
  • Find the last activity of an Idle Blocker
  • Monitor Deadlocks
  • Use MySQL as a DPA repository
  • Support for the latest databases - SQL 2016 and MySQL 5.7
  • AD/LDAP Configuration Wizard
  • Tomcat 8 and Java 1.8
  • Lots of minor fixes - see the Release Notes.

A major theme in this release is blocking and locking analysis.  Blocking occurs when the database locks an object to perform a transaction and another request for this same object is received.  The database blocks the second transaction until the original transaction is complete and the lock is released. Locking is a good and necessary thing for a database to function properly, but excessive blocking causes programs and clients to wait, and is very difficult to diagnose.  DPA 10.2 is focused on solving difficult blocking and lock problems with just a few clicks.

Identify Root Blockers causing the greatest impact

Blocking can involve more than just two transactions, as transaction A blocks transaction B, B blocks C, and so on, piling up the time applications are waiting quite quickly.  DPA 10.2 not only identifies the root blockers, but also aggregates the wait time in the correlation tab to show which queries are causing the most overall wait.  If one query is the root blocker for other queries, say from your online website store, customers may be waiting - which can impact your bottom line.

This new blocking summary information appears as a correlation tab under the wait time chart and tracks with your choices of dimension and time slice (from 30 days to 1 sec)... even the colors of the queries in the bar chart matches.

pastedImage_5.png

Bonus 1: You can toggle on the idle blockers (gray bar), to see if they are causing a lot of wait.

Bonus 2: We also have a Top Waiters view, so you can quickly find which queries are most impacted by blocking as well.

Find the last activity of an Idle Blocker

Imagine a construction crew blocks the road you're on, and while waiting for them to complete their work, you notice they stop jack hammering and walk away.  You are being blocked, and your blocker is idle - they're doing something (maybe eating lunch), but they sure aren't working on the road.  This is analogous to an idle blocker, where a program or user locks an object in the database, but then performs work outside of the database before completing the transaction.   From the database's perspective, the blocker is "idle" because its not doing database work, and there is no association with database activity.

So when you try to diagnose an idle blocker, you have no starting place, nothing to pass on to the developer other than "it's idle".  Often times, the DBA would have to spend hours sifting through queries to identify the last activity before the lock went idle, so that the problem could be found and fixed. Well, you don't have to do that manual work anymore - with a couple of clicks, DPA will take you back to the last known activity of an idle blocker, and let you easily annotate it too.

To find the last activity, drill down to an hour and click the Blockers tab. On the left of the table, root blockers are shown, and you can expand the tree to see all the waitiers.  For Idle blockers, there is a new link called Find Last Activity on the right.

pastedImage_1.png

When you click Find Last Activity (ex SPID 79), a popup appears with a couple of choices - Annotate and View Activity, or just View Activity.

pastedImage_3.png

If you choose "Annotate and View Activity", DPA will search the past 48 hours for last activity for that session related to the blocker (Session 79).  We've now found the last activity before the block went idle.

pastedImage_5.png

And we annotated it, so its much easier to find in the future!

pastedImage_6.png

Now you have something you can share with your team and quickly identify and fix the problem!

Monitor Deadlocks

A deadlock happens when two or more transactions block each other by holding locks on resources that each of the transactions also need.

DPA uses a modified Extended Event session in order to capture deadlock details for SQL Server, and will use the default system health session for SQL Server on Amazon RDS. Users are able to configure a non-default session using the Advanced Properties page. When deadlocks are detected, they are displayed at the bottom of the Trends tab:  DPA also shows a metric named Victim Impact, which is a measure of time the victim's transaction was running and consuming resources.

Deadlock analysis is contained in the correlation tab with two options - Deadlock List and Deadlock chart.  The deadlock list contains a list of all the deadlocks for the current timeslice.

pastedImage_0.png

Clicking on a deadlock link takes you to a deadlock details page which has three parts.

1. Deadlock Summary, which shows the Total Victim Impact.
Bonus:  Open the deadlock XML in SQL Server Management Studio and see the visualization of the deadlock or email the deadlock information with a click of the button!

pastedImage_2.png

2. Victims and Survivors: Shows you details of the queries that were victims and thus rolled back, as well as the survivor which completed.

pastedImage_3.png

3. Deadlocked Resources - Bonus: All those links have popups with definitions and expert advice.

pastedImage_2.png

The Deadlock chart simply counts the number of deadlocks occurring in the same dimension and time slice you are viewing, helping you identify trends.

pastedImage_0.png

For a deeper dive on the nature of deadlocks and how to analyze them with DPA, check out our deadlock analysis KB article.

MySQL as a DPA Repository

When we released monitoring for MySQL in DPA 10.0 less than a year ago, our customers were excited to finally be able to monitor MySQL with the same wait time analysis methodology as used for SQL and Oracle.  However, there were lots of folks who wanted DPA to use MySQL as a data repository too.  I am happy to say you have the option to use DPA as a data repository!  If you want to change from SQL or Oracle, we don't migrate old data when changing repository types.

New way to get to Historical Charts

A new trick that's easy to miss is the addition of a link to the historical charts wherever you open the query popup.  There is a new button in the bottom left that allows you to jump to historical charts from almost anywhere. Its a small but welcomed change.

pastedImage_2.png

But Wait, There's More

Every release has some minor features too, and 10.2 is no exception.

  • Support for the latest databases - SQL 2016 and MySQL 5.7
  • AD/LDAP wizard now makes it much easier to connect to your directory.  Current configurations are supported and you can modify them via the Wizard.
  • Tomcat 8 and Java 1.8 moves DPA to the latest versions with security updates.
  • Lots of minor fixes - see the Release Notes.

Sign up to get on the next beta!

The dust hasn't settled on 10.2, but our dedicated team of database nerds and code jockeys are already hard at work on the next awesome DPA release - check out the What We Are Working On for DPA (Updated May 11, 2016) post.  If you don't see everything you've been wishing for there, you add it to our Database Performance Analyzer Feature Requests

If you want to get your hands dirty as soon as our next beta is ready, get on the list for the next beta by filling out this survey.

Thwack - Symbolize TM, R, and C