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 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
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.
- 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
Formatted SQL text that is easy to read, as well as easy to copy.
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.
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 November, 2019) 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.