cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

DPA 12.0 Part 2: Table Tuning™ Advisor

Level 11

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.

pastedImage_1.png

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

pastedImage_3.png

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.

  pastedImage_4.png

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.

  pastedImage_6.png

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.

  pastedImage_8.png

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.

pastedImage_9.png 

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.

  pastedImage_15.png

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.

9 Comments

Hi guys.

I'm having a timeout error when the Table Tuning Advisor tries to collect information from very big tables.

DPA Version: 12.0.3074

Repository: SQL 2014 SP2

Monitoring target: SQL Server 2008 SP4

Error message:  I/O Error: Read timed out; nested exception is java.sql.SQLException: I/O Error: Read timed out at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:105) ecc...

Basically, as tracked in the log file, it seems DPA is executing a DBCC command against the target  with a timeout set to 180 seconds.

Any way to extend this timeout to at least 600 seconds ??

Thanks.

Level 11

You will probably want to open a support case so we can get your log files and take a look. Alternatively, send your log files to me @ dean.richards@solarwinds.com.

Hi Dean.

Case opened

NR: #00177381.

Level 11

OK great, you will be in good hands with our DPA support team.

Level 8

Hi,

i was face same issue, it's related to database resources where DAP resp installed.  after i upgrade the memory problem solved. may that help you.

Level 7

Hi,

I have a problem with index informations. Two days ago few indexes has information last used: Not since instance startup 105 d ago, and stats generatet 8h 32s ago. Today the situation looks completely different. There are no unused indexes, although nothing has changed in the structure of the database. And stats generated is 4 d (two days ago was 8h)? What's going on?

best regards

Ola

Level 11

Did you see this issue for an index in an Oracle or SQL Server instance? DPA is getting the data from system tables, but it is strange that stats generated is now 4 d when 2 days ago it was 8 hours. Do you have to have any screenshots showing this? Can you reproduce this issue?

Level 7

Thanks for your replay. I saw it in SQL Sever 2012 SP2 instance. Below I paste the example of old and today screenshots - unfortunately without dates ... DPA previously showed that the indexes are not used and two days later they are already used - despite the fact that neither the database nor the application havn't changed. The statistics time didn't match, but unfortunately I don't have this in screenshots. Is it possible?

pastedImage_1.png

pastedImage_3.png

Level 11

It seems like this could be possible if the table was in the instance for the whole time, i.e. 105 days, but then the indexes started being used recently for whatever reasons. When you are on this page, in the top section it should show the SQL statements that are accessing this table. Did any of them have a plan change around the same time you saw this behavior and start using these indexes? Did some of them suddenly start executing 2 days ago (or maybe 4 by now)? Are these indexes newly created recently? If so, and there was no usage information, DPA does default back to instance startup which may not be quite correct.

DPA gets index usage information from the sys.dm_db_index_usage_stats DMV where we look at the following list of columns: last_user_seek, last_user_scan, last_user_lookup, last_user_update, last_system_lookup, last_system_scan, last_system_seek, last_system_update. For DPA to show 105 days ago, my guess is that these were all blank and that's how long ago your instance was restarted.

Can you think of anything that is unique about this table and it's indexes that may cause DPA to miss the usage data? The table ends with _TEMP, is there a special use for this table? Is it a special type?

About the Author
Database performance tuning guru, love all things related to performance.