I am happy to announce the General Availability of Database Performance Analyzer (DPA) 12.0. This release focuses on analysis with two major features: Query Performance Analyzer (QPA) and Table Tuning Advisor. We have also improved our integration with the Orion® Platform by adding blocking, deadlocks, and wait time status to the PerfStack feature. In this post, I will cover Table Tuning Advisor, while QPA will be covered in another post.

 

Table Tuning Advisor

Every database has inefficient queries—ones that perform many logical reads but retrieve a relatively small number of rows. In other words, they do a lot of work for a small return. This type of inefficiency can result in higher I/O, longer wait times, greater amounts of blocking, and increased resource contention.

 

Tuning inefficient queries can be difficult and many questions tend to surface as part of the process. DPA 12.0 with Table Tuning Advisor can help lead you to answers to some of these common questions.

  • Should you tune the query? Add a new index? Or maybe add columns to an existing index?
  • Plans are complex and hard to analyze; which steps are the ones I should pay attention to?
  • Which predicates in the plans are causing inefficient data access and high amount of reads?
  • Are there recommendations I can use as a starting point?
  • Are there other inefficient queries that access the same table and could be affected by indexing decisions?
  • How many indexes currently exist on the table and how are they designed?
  • How much data churn (inserts, deletes, and sometimes updates) does the table undergo?

 

DPA’s Table Tuning Advisor is designed to analyze expensive queries and plans to help identify tables that have inefficient workload run against them. For each table, the advisor page displays aggregated information about the table and the inefficient queries. You can use this information to make informed decisions about database performance optimization opportunities, and to weigh the potential costs and benefits of adding an index.

 

Navigation

There are two ways to get to the advisor page:

  • A new Tuning super-tab near the top of the page appears after clicking into an instance. This will take you to a page that combines the Query and Table Tuning Advisors.

  • The new Query Performance Analyzer (QPA) page with the Table Tuning Advisors section provides a summary of the advice aggregated to the table level and includes links to the advisor detail page.

Advisor Page Layout

The Table Tuning Advisor page has three main areas:

  • Inefficient SQL – a list of queries accessing the table ranked by relative workload.
  • SQL and Plan Details – SQL and Plan details for the selected query.
  • Table and Index Information – current table information, existing indexes on the table and the table’s columns.

 

 

Table Tuning Advisor Example

Let’s assume we are being proactive and want to tune something that will have a big impact. At a summary level, the tuning tab shows the tables with inefficient queries and ranks them based on workload. The list includes an aggregated view of wait time for each table, the number of queries that have inefficient plan steps on the table, and the number of index recommendations. This list quickly gives insight into the tables that have the highest inefficient workloads executing against them. These are prime opportunities for tuning.

 

Are there any recommendations to use as a starting point? Clicking on the “orders” table takes us to the Table Tuning Advisor page that provides details about inefficient queries accessing the table. This page pulls together what you need to know about the table regarding inefficient usage patterns, statistical information, design of current indexes, and much more. Index recommendations appear near the top of the page and may provide a good starting point for a solution.

 

Which steps in the plans are inefficient and does it align with the recommendation? DPA uses a proprietary algorithm to find plan steps that are inefficient and causing issues. Inefficient “producer” steps (for example, full table/index scans) read data to be processed later by subsequent "consumer" plan steps. While consumer steps (for example, sorts) can have a high plan cost, they are usually affected by a preceding producer step that read too much data. DPA can point out the inefficient producer plan steps that should be the focus of tuning efforts.

 

In this example, DPA identified two steps that are inefficient:

  1. INDEX SCAN – Step 64 – A full scan of the o_totalprice_index index. Notice the predicate value that shows a function named CONVERT. The query is using a CONVERT function against the o_totalprice column which will often negate effective use of an index. An INDEX SCAN reads the entire index, which is why the step shows 15 million rows associated with it.
  2. CLUSTERED INDEX SCAN – Step 69 – A full scan of the orders table. Notice the CONVERT_IMPLICIT function within the predicate value. This indicates an implicit conversion, i.e., data type mismatch, and DPA displays a predicate warning as a result. Click on the warning to get additional information. Other potential warnings include:
    1. Lookup Warning – The plan uses an index but is required to go back to the table to “look up” other needed information. Adding a “covering” index can potentially help tune this issue.
    2. Spool Warning – The plan step is storing data for later use, but large amounts of spooling can cause disk overhead.
    3. Parallel Warning – DPA has detected a parallelism step later in this query's execution, implying that this step's intermediate result set is likely large enough to exceed parallel processing cost thresholds. Look for ways to rewrite the query to reduce the size of intermediate result sets earlier in the query. For example, look for a sub-select that could produce fewer rows.

 

Based on the data shown by DPA in this example, the index recommendation may help tune the clustered index scan in step 69. However, tuning step 64 will likely require a modification to the query to remove the CONVERT function on the o_totalprice column. Gleaning this information via manual plan analysis would probably take hours. Plan analysis is difficult, so let Table Tuning Advisor help get you to a good starting place.

 

Are there other inefficient queries that access this table? The left pane of the Table Tuning Advisor page shows other inefficient queries, ranked by relative workload, accessing the “orders” table. Pay attention to the queries near the top of this list because they cause more workload against the table. Conversely, you should not spend as much time on queries near the bottom with small relative workloads. These queries could be affected by a new or modified index on the table.

 

How many indexes currently exist on the table and how are they designed? Toward the bottom of the Table Tuning Advisor page, the current indexes and their columns are shown along with information about statistics and usage. Also shown are fragmentation percentages, sizes of the table and indexes, the table’s columns, and more. This is important for several reasons:

  • Is the data churn for the table high? If so, this means insert/delete activity is high and a new index could cause more harm than good.
  • Is there an existing index that already contains the o_shippriority column? If so, can the index be modified to benefit this query versus creating a new index?
  • Were optimizer statistics generated recently? If not, and churn is high, updating the statistics for the table may be a good first step.
  • Are indexes fragmented? If they are and scans are performed against them, defragmenting them may help performance.

 

What Did You Find?

Our development team uses DPA to help make sure our code performs well. When using the Table Tuning Advisor, it pointed them to a problematic set of tables. Within a couple of hours, they tuned the queries with a simple rewrite and saved hours of database time every night during the cleaning process. If you find interesting stories in your environment, let us know by leaving comments on this blog post.

 

We would love to hear feedback about the following:

  • Does this improve your workflow when tuning a query? How much time does it save you?
  • Are there tuning questions that are not answered by the page?
  • Is all of the assembled data important to you when tuning?

 

What’s Next?

Don’t forget to read Brian’s blog about Query Performance Analyzer (QPA). To learn more about other DPA 12.0 new features, see the DPA Documentation library and visit your SolarWinds Customer Portal to get the new software.

 

If you don't see the features you've been wanting in this release, check out the What We Are Working On for DPA post for what our dedicated team of database nerds 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.

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.

 

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.

Filter Blog

By date: By tag:

SolarWinds uses cookies on its websites to make your online experience easier and better. By using our website, you consent to our use of cookies. For more information on cookies, see our cookie policy.