Showing results for 
Search instead for 
Did you mean: 

DPA 12.0 Part 1: Query Performance Analysis and PerfStack

Level 15

To kick off the Q3 systems releases, I am happy to announce Generally Availability of Database Performance Analyzer version 12.0.   This release focuses on analysis with two major features: Query Performance Analyzer (QPA) and the Table Tuning Advisor.  We've also improved our integration with Orion® Platform by adding blocking, deadlocks, and wait time status to PerfStack™. In this post, I'll cover QPA and the Orion integration. Table Tuning Advisor will be covered in another post.

Query Performance Analyzer

QPA is designed to intelligently assemble current and historical data for a query, combining all the information about a query into one place, including the query analysis (summarized per day) and the historical charts (30 days of data, down to 10-minute intervals). QPA analyzes the data about the query and automatically expands sections and selects metrics to show you the most relevant data. It also allows you to change time ranges on the query, and still has the great drill-down capability you are used to. You can use QPA for queries in any database supported by DPA.

Since QPA has all the data you previously saw on multiple screens, all links on query hashes and names now go to QPA, keeping your current timeframe. So now, when you go to look at a query in the product, you get QPA!

New Charting Capabilities

QPA uses SolarWinds' new Nova GUI components, allowing us to assemble and present data in new ways. We are very excited to have adopted this technology. There are a few nifty features that you'll see in the screenshots below.

  • Charts all have the same x-axis, even if their data is at different frequencies or ranges
  • As you roll over the chart, the values and time are shown both for the chart you are on and all other charts displayed
  • In all charts, you can uncheck one of the items on the legend to remove it
  • When you roll over an item in the chart legend, it is highlighted while other items are grayed out

All of these combine to make it very easy to inspect and correlate data across multiple charts.

QPA Layout

QPA has two main areas:

  • The Wait Type Chart and Time Navigation
  • Three tabs showing different data and analysis


Top Chart - Wait Types and Navigation (yes it's sticky!)

DPA is all about waits, so the top chart shows the total wait time by wait type, and it is sticky so it stays at the top of the page, making it easy to correlate the waits with the data in the charts below it. The new time navigation at the top of the chart allows to you to choose a pre-defined time range or build your own.  And now, you can display data further back than 30 days if you need to.

Tabs - Intelligent Analysis, SQL Text and Supporting Data

QPA has three tabs which we cover in detail below.

  • Intelligent Analysis: Intelligently assemble and display the most relevant data about this query
  • SQL Text: A nicely formatted version of the SQL text
  • Supporting Data: Additional performance data about this query available in under 24 hours

Intelligent Analysis

QPA can intelligently assemble the most important information about a query and allow you to customize your view to meet your needs. Intelligence includes expanding sections to show you relevant data and picking metrics based on the predominant wait type.

Sections include:

  • Query Advisor: Latest advice for the query in the current time period
  • Tables Tuning Advisor: Latest Table Tuning Advisors for the query in the current time period
  • Statistics: Query statistics, both the actual value and per execution
  • Blocking: Shows blocking info (blockee and blocker) if it sees significant blocking
  • Plans: Shows plan information if more than one plan is used for the current time period
  • Resource Metrics for the Instance: Displays instance resources based on the predominant wait time

Here is a query with both Query Advisors and Table Tuning Advisors.


Keep scrolling to see multiple plans and PLE (and more CPU/memory resources). Note that:

The wait type chart shrinks and stays at the top of the page

Rolling over a chart shows detailed data on each chart

QPA selected which sections to expand and which metrics to show


SQL Text

Formatted SQL text that is easy to read, as well as easy to copy.


Supporting Data

Supporting data is additional per-query data we collect and is only available at timeframes of 24 hours or less.  Sections are auto-expanding if DPA detects interesting data.


Analyzing a Query with QPA (Example)

If we look at the following query for 30 days in QPA, we can see that wait time started increasing around April 23. The query advisors show advice for the latest day (just like on the trends page), but instead of drilling, let’s scroll down the page some


I see the number of executions is unchanged, but wait time per execution increased with wait time... so it looks like something changed.


If I keep scrolling, I'll see that the blocking section is closed (so no blocking), but the plans section is open and showing multiple plans. DPA noticed a plan change and displayed this chart automatically. If there was only one plan, DPA closes the chart and just gives you a link to the plan.

Note that increase in wait time and wait time per execution correspond to the same time as the plan change on April 23—BINGO!


If I want to see more detail on April 23, I can drill by clicking on the bar chart (just like on the trends page).  I can click it on the top chart, or any other bar chart (like the plans chart).

When I drill into Apr 23, I can see that the change correlates to the plan change. Note that I can also see the instance statistics, and they don't indicate any kind of resource pressure.


From here, I can drill down to an hour if I want, or I can click the plan hashes and take a look at the differences between them.

Blocking, Deadlocks and Wait Time Status in PerfStack

We don't have a new DPA Integration Module (DPAIM) for the 12.0 release, but PerfStack is so versatile, we can share new data with it and have it available automatically. Now blocking (root blocking and blockee), deadlocks, and wait time status are available in PerfStack.


When you highlight the blocking info, you can see the queries in the data explorer.


Find. Analyze. Optimize.™ with DPA.

What did you find in your environment?

We'd love to hear your story about queries and indexes you've improved in your environment. Feel free to post your stories here and commiserate with your fellow admins. For example, during an RC-assisted upgrade, we helped a customer upgrade and walked through the new features, and in just a few minutes, we found a query with over six hours of wait time in QPA. By drilling into the new Table Advisor, we were able to discover the table was missing an index.

What's Next?

Don't forget to read Dean's blog on the Table Tuning Advisor and the DPA 12.0 Release Notes

If you don't see the features you've been wanting in this release, 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​.

Level 17

Wonderful new features! I especially love the Perfstack enhancements. This was an eye-opener at VMworld and Microsoft Ignite as people were excited to see metrics from all the different layers in their enterprise.

Level 21

Sigh.  Looks like heaven.  How to afford it, and break it through into the DBA's silo remain the tasks at hand.

Level 13

Participated in the beta on this.  There are a lot of really nice improvements.  We've been on it for a few months now and it works great.   Well done.

Level 10

rschroeder-   my tact was simple.  I convinced my operations manager to buy once license to "move" to where ever the biggest fire was.      DBA's moaned and groaned about hooking up the credentials initially.  ("what do you mean you need SA temporarilly???"  )  But once DPA identified the long chronic problem with that system- word traveled fast.  Purchases have come in waves.   3 here, 5 there, our last purchase was 10.     We are at 25 now I think.

What is really nice is our DBA team is finally understanding its not there to "replace" anyone and are starting to request inactive licenses be put on servers they havent been able to spend much time one to figure out.  

Level 7

The new version is pretty cool in a lot of ways.  I am struggling a little with "view historical charts" from version 11 now being all in one long page, and I'm VERY disappointed in the missing summary statistics for a given hour.  It used to be when you drilled into a specific SQL, you would see a summary that included data for all the "view historical charts" data. 

For exmaple, you'd see in this hour, there were

executions:     52          rows processed:      728parses               1          Disk Reads               12,945,997

sorts               52          Buffer Gets                    12,942,492

all in a neat summary graph at the top of the SQL Data piece.  I have not been able to find this in the new 12.1 release.  This summary is one of the things I had my developers look at with an eye to 10 rows/buffer = great, 100 rows/buffer = o.k...more, let's look and see what we can do to improve that.  Please bring this summary data back!!

Level 15

Hi - thanks for the excellent feedback. We made QPA the default action, but you can still get to the hourly view of the query.  When you drill down to an hour, there is a new filter to the right, just select a query there and you can see the old view.



On the new view, these are represented as charts over time, when you roll over the charts, you can see the values.  This is handy because they align with your wait time too. You can select which statistics you want to see on the chart.


Hope this helps.


Level 7


   That does, indeed help!  It feels kinda hidden, and different from normal DPA navigation as clicking one place takes you to one view but drilling the normal way takes you elsewhere, but I'm very happy I can still get to this view!  The charts are indeed useful, but this one summary tells quite a story about a given SQL, including helping us see a sudden rise in executions when the development team tells us "nothing has changed."  I do training for our developers at my company, so it will be very helpful to still have this view for them, though!


Level 10

I am extremely pleased that the summary view didn't go away!!

About the Author
Ok, so I have been a geek for a long time, went to UT finally ended up at a startup, Tek-Tools where we built a monitoring and reporting product for Servers, Backups, Storage and Applications. In Jan 2010, Solarwinds bought Tek-Tools, and I was added to the PM team. When my mind actually wonders from making our products better, I am generally spending time with my family, reading, watching sports, arguing politics or tinkering with stuff (it can always be made better), with the occasional camping trip thrown in for good measure.