If you are using Orion, I am sure you have already heard of PerfStack (aka Performance Analysis), SolarWinds' Drag & Drop Answers to Your Toughest IT Questions. DPA did not make the first cut of PerfStack, but I am happy to announce that with the release of DPA Integration Module 11.1, you can now see DPA goodness in PerfStack too!!! Imagine expanding Using PerfStack with WPM and SAM to Troubleshoot Web Performance Issues to include database and query wait time! Answer the question "Is it the database or the application?" in just a few clicks, pulling in whatever data you need from both DPA and Orion!
PerfStack Support for Databases monitored by DPA
DPAIM 11.1 exposes DPA data already available in other Orion views, but also adds some new ones as well! Data includes:
- Total Wait Time
- Wait time by dimension (SQL, Wait Types, Programs, Users, etc.) with drill downs to SQL text and Wait Type descriptions
- Database metrics
- VM metrics
- Custom metrics (yes, even the custom metrics!)
Data History and Granularity
PerfStack is able to show all the data available in DPA. So if you have 5 years of history in DPA, you'll see it in PerfStack. As you drill down in PerfStack, you'll get to more granular data (down to 1 second wait time). PerfStack will choose the best granularity for the time range of your current view, and then adjust accordingly as you zoom in or out.
PerfStack allows you to stack different wait time dimensions in the same view to help you solve problems quickly. For example, do you want to know who is writing those bad queries? Just stack SQL and user dimensions into one view and look for the correlation. You can also stack dimensions from different databases - even if they are on different DPA servers.
Even more DPA data is available in PerfStack's Data Explorer function. The Data Explorer lets you drill down and view the details of the wait time dimension you've highlighted in PerfStack. All Wait Time dimensions will show you a list of metrics ranked by total wait time. but a couple dimensions show extra information:
- Total Instance Wait Time (SQL) will show you the SQL text, letting you find the query causing the issue.
- Wait Time by Wait Type will show you descriptions of the Wait Type, helping you understand why your queries are waiting.
Data Explorer - Query Search
When you drill into the Data Explorer for Total Instance Wait Time (SQL), you'll see the SQL text for the queries with the most Wait Time. However, you can search the text listed in the Data Explorer - say a specific table, group by or other parameter - to further filter the result set.
Predefined PerfStack Views for Databases
Every database displayed in Orion via DPAIM has a predefined link to a default PerfStack view, which will include both wait time and key metrics for the specific database.
Adding Database Data to PerfStack
To add database data to PerfStack, click Add Entity then scroll and select Databases Instance.
PerfStack will list all databases from all integrated DPA servers.
Once you've selected an instance, you can choose multiple wait time metrics to add to PerfStack.
Once you've added wait time metrics, selecting an area will allow you to drill to additional information on some metrics: SQL text (for Total Instance Wait Time) and the wait type description (for Wait Type).
To see a fully operation PerfStack example, check out this PerfStack Application to Database Mapping in the Orion Demo Site.
New Resource - Blocking & Deadlocks
A new resource available for every instance monitored by DPA will show you blocking (all databases) and deadlocks (SQL Server only), to let you quickly see if the source of a database problem is blocking or deadlocks.
The resource has three tabs to show which queries are waiting the most (Top Waiters), which queries are blocking the most (Top Root Blockers) and if there are deadlocks.
Clicking on the SQL hash will get you a query detail popup, with a link to the DPA historical view for that SQL.
Improved Resource - All Databases Instances
When we first created the resources for the DPA Integration Module, we tried to communicate as much information as possible, combining DPA wait time and performance data as well as data in Orion. However, with so much information, it made it too difficult for users to quickly answer the question "is the database having a performance issue". Yes, memory may be high, but if users aren't waiting on queries any longer than normal, then there isn't really a "performance" issue. Based on this feedback, we've made a few changes:
- In the last release (DPA 11.0), we changed the status shown in Orion to reflect DPA's wait time status only
- In DPA 11.1. we've simplified resources to only show wait time status and not the status of advisors or metrics (CPU, Memory, etc.)
- Wait time status now has three states (green, yellow, red) instead of two (green, red)
Basically, we want to make it really clear if there is a problem with the database performance issue (i.e. high wait time) before you drill down to additional data.
Database Summary View - All Database Instances Resource
For databases monitored by DPA, the status is determined by the wait time status from DPA, regardless of other indicators in DPA or Orion. For databases monitored by Orion but not DPA, the Orion status is shown.
The new resource has simple filters at the top, allowing you to select one or more statuses. In this case I've filtered the resource to only see database instances with a red or yellow status.
Other resources have been improved as well, see the DPAIM 11.1 Release Notes for more information.
Support for SQL 2017, Oracle 12.2, MariaDB and more...
As usual, all the databases that DPA supports you'll see in Orion too. Here are the newest databases supported by DPA:
- SQL Server 2017 on Windows
- SQL Server 2017 on Linux
- Oracle 12.2
- MariaDB 10.0, 10.1 and 10.2
- IBM DB2 11.1
See a full list of Database versions you can monitor with DPA.
DPAIM for ALL DPA Customers!
In the past, since DPAIM was an "integration" module, it was not available as a standalone module, meaning you needed to own another Orion product to use it. However, with the addition of PerfStack support, we wanted to make sure all DPA customers could enjoy the benefits of DPAIM and Orion too. So now, DPAIM is a standalone module, and doesn't require to be installed with another Orion product. All DPA customers can now install the DPAIM module and take advantage of the great features of the Orion platform.
How do I get the DPA Integration Module?
If you own DPA, you can get DPAIM 11.1 multiple ways.
- If you have a SAM installation, when you upgrade to v6.5, DPAIM 11.1 module will be installed and ready to use.
- If you have other Orion-based products installed (NPM, VMan, SRM, etc.) but not SAM, you can download DPAIM from the Customer Portal and install it on your Orion server.
- If you own only DPA but no other SolarWinds Orion product, you can take advantage of DPAIM too. Simply download it from the Customer Portal and install the DPA Integration Module (DPAIM) on another server. If you are using a SQL Server for your DPA repo, you can use the same one for DPAIM. If not, you can opt for SQL Express bundled with the DPAIM installer.
Once you have DPAIM installed, follow these instructions to integrate it with DPA.
But wait, there's more!
See everything else that made it into the release in the DPAIM 11.1 Release Notes, including:
- Improved Instance View - Database Response Time Resource
- New Query Popup, with formatted SQL
- Compatibility Checker
Don't see what you are looking for here? Check out the What We Are Working On for DPA (Updated August 29, 2018) post for what our dedicated team of database nerds and code jockeys are already looking at. If you don't see everything you've been wishing for there, add it to the Database Performance Analyzer Feature Requests.