DPA 12.0 Part 2: Table Tuning Advisor

darichar over 5 years ago 8 minute read time

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:

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