Database Performance Analyzer (DPA) 2024.2 Is Now Generally Available

mandevil 7 months ago 5 minute read time

DPA 2024.2 Release

SolarWinds proudly announces the general availability of Database Performance Analyzer (DPA) 2024.2! You can download the latest version of DPA from your customer portal. This release includes PostgreSQL workload analysis by bringing the much loved and very powerful DPA existing features for SQL Server and Oracle of execution plan collection, Index Advisors, and Table Tuning Advisors to PostgreSQL. Please reference the official release notes for a complete listing of release content.

Table Tuning Advisor

Every database has inefficient queries—ones performing many reads (either logical or physical) but retrieving a relatively small number of rows. In other words, they work very hard 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. 'Questions such as:

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 should I pay attention to?
Which predicates in the plans are causing inefficient data access and high amounts 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 structured?
How transactional is the data in the table in the form of data churn (inserts, deletes, and sometimes updates)?

Table Tuning Advisors can help resolve these questions.

'Table Tuning Advisor is designed to analyze expensive queries and plans to help identify tables with an inefficient workload run against them – regardless of the reason. 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 "Tuning" super-tab near the top of the page appears after clicking into an instance. This will take you to a page combining the Query, Index, and Table Tuning Advisors.

Once drilled into a specific Query Performance Analysis (QPA) page, scroll down to the Table Tuning Advisors section which summarizes the advice aggregated to the table level and includes links to the advisor detail page.

Advisor page layout

The Table Tuning Advisor page is densely packed with key intelligence related to the object selected and 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 to have a significant impact. At a summary level, the tuning tab shows the tables with inefficient queries and ranks them based on inefficient workloads. The list includes an aggregated view of wait time for each table and the number of queries with inefficient plan steps on the table. 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, which details inefficient queries accessing the table. This page pulls together what you need to know about the table regarding inefficient usage patterns, statistical information, the design of current indexes, and much more.

Which steps in the plans are inefficient? DPA uses a proprietary algorithm to find inefficient plan steps 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 cost, they are usually affected by a preceding producer step reading too much data. DPA can point out the inefficient producer plan steps to be the focus of tuning efforts.

In the example above, DPA identified two plans used as well as an inefficient step in each plan:

SEQ SCAN – Step 7 – A sequential scan of the "customer" table. Notice the predicate value is showing a similar comparison being used, which can be very inefficient. The sequential scan caused an examination of 150K rows for a matching phone number.

Getting this information via manual plan analysis would probably take hours. Plan analysis is difficult, so let Table Tuning Advisor help get you to a great starting place.

Are there other inefficient queries with access to this table? The left pane of the Table Tuning Advisor page shows other inefficient queries, ranked by relative workload, accessing the "customer" 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. Sometimes, a small adjustment like an added or modified index can make a big difference for many inefficient queries.

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. Other table and index metadata is displayed after a real-time query to reflect the most up-to-date information. This is important for several reasons:

Is the data churn for the table high? If so, this means insert/update/delete activity is high and a new index could cause more harm than good.
Is there an existing index containing the column called out in the predicate that could help? 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 bloated? If they are and scans are performed against them, check the health of the vacuuming process.

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 few 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 the page does not answer?
Is all of the assembled data important to you when tuning?

What's next?

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 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.