Product Blog

2 Posts authored by: darichar

I’m happy to announce the General Availability of Database Performance Analyzer (DPA) 12.1. This release focuses on deeper performance analysis and management of DPA through these cool new features:

  • Anomaly Detection Powered by Machine Learning
  • Management API
  • Upgraded Java
  • New Options Page
  • Alerting Improvements

Anomaly Detection Powered by Machine Learning

Users tend to log help desk tickets when things are running slower than normal, i.e., an anomaly. Those tickets often find their way to the database team’s inbox to check the database. DPA can be used to find issues when you have time to drill into the wait time data, but often, time is of the essence. Everyone wants answers immediately.

 

Tired of comparing the trends chart with previous days to decide what “normal” looks like? DPA 12.1 now does the work for you, using a machine learning algorithm to identify which hours are abnormal, and displays the information contextually on the trends page. Bonus! If DPA detects an anomaly in the last 60 minutes, it changes the wait time status on the home page, letting you quickly identify the database instances your users are waiting on.

 

The DPA wait meter on the home page is now powered by anomaly detection, and new correlation charts appear as you drill into an instance. For example, you may be reviewing the home page and suddenly see the wait meter turn red.

 

This is an indication the instance is having higher than normal wait times and may be having issues. Clicking on the wait meter takes you to a view of the last 24 hours, and the status of the last bar will match the wait meter.

 

Drilling into the last bar, we can start to unravel the root cause of the anomaly. In this example, we see heavy wait times on RESOURCE_SEMAPHORE_QUERY_COMPILE, usually an indication that one or more queries require more memory than is currently available. In our case, many queries were waiting on this wait type, indicating a potential memory shortfall on the database server, which is what we found to be the case. Without the anomaly detection feature, we may not have known about this problem.

 

For more about this story and others, see this feature post in the DPA Customer Success Center: DPA 12.1 feature: Anomaly detection - SolarWinds Worldwide, LLC. Help and Support .

Management API

DPA has many customers automating tasks within their database environments, and many of you have scripts that can deploy/destroy a database environment in minutes. The new REST API in DPA 12.1 can be used to further that automation to management of DPA itself as well as monitored instances. It can safely connect to DPA and issue calls to:

  • Add and remove instances
  • List, allocate, and deallocate licenses
  • Stop, start, and update passwords for monitors
  • Add, retrieve, and delete annotations
  • And more

 

DPA customers are already using the API to:

  • Create annotations when a new build of an application is installed
  • Add monitoring to a newly created database instance and allocate proper licenses
  • Stop and restart monitors before and after O/S patches

 

If you are using the DPA API to do cool things, reply to this post and let us know about it.

 

For more information about DPA’s Rest API, including an interface to try them out before building code around them, use the new Options page and the Management API Documentation link. Here’s a list of other useful pages when you are ready to put the API into action:

What Did You Find?

Our QA team uses DPA to help make sure our code performs well. The anomaly detection feature has helped them be more efficient when problems crop up. DPA pings them using anomaly detection alerts rather than a person being required to drill into every instance to find issues. They can then use the anomaly detection charts to quickly understand the issues. 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 anomaly detection improve your workflow for finding wait time issues?
  • Are there issues in your databases that DPA did not find, or flagged incorrectly?
  • Are you using the REST API? How much time does it save you? What processes are you automating?

What’s Next?

To learn more about the exciting DPA 12.1 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.

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?

 

Find. Analyze. Optimize.™ with DPA.

 

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.

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.