cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
cpriddy Product Manager
Product Manager

New in 2019.4! Best Practice Analysis for Table Tuning Advisor

Database Performance Analyzer has now added a new Table Tuning Best Practice Analysis feature. Basically, this feature analyzes a set of best practices against table definitions (e.g., primary keys, indices on foreign keys, etc.). The results for each best practice are displayed in the Table Tuning Advisor. For application developers or DBAs reviewing and/or tuning a table and its queries, this is a quick reminder of recommended table definitions that should be followed.

Below is an example showing a table has passed all of the best practice checks.

Screen Shot 2019-05-17 at 10.46.04 AM copy.png

Where to view the Table Tuning Best Practices

From the home page, click on a Database Instance name where the Tuning column indicates either a Warning or Critical advisor issue.

Below the DBPABIRDYS database is showing a warning in the Tuning column.

BP 1 Home DPABIRDYS.png

Clicking on the yellow triangle hyperlink in the Tuning column for the DPABIRDYS database instance will take you immediately to the Tuning Advisor for the database. On the left is the list of Query Advisors for the instance. Under the right column is a list of tables in the database that DPA has identified as candidates for improved performance.

BP 2 Tuning Advisors Page.png

Clicking on the CONTIME table name hyperlink will bring you to the Table Tuning Advisor page (below) for that table.

The blue "i" circle and associated text at the top right of the Current Table Information section indicate that 1 out of the 5 best practices is not being followed for this table.

BP 3 CONTIME table Tuning.png

Hovering your cursor over the blue "i" circle will pop up a list of all the best practices analyzed and the status of each.

We can see that a wide index has been identified which either has too many columns or its overall size is excessive.

BP 4 BP popup.png

The wide index (PK__CONTIME__0DA06EB2EDD3EB57) is also detailed in the "EXISTING INDEXES (1)" expansion section.

Note: if you want to choose which best practices are analyzed and/or configure different best practice threshold values, you can do so in the Advanced Options page. All of the best practice option names are prefixed by "BEST_PRACTICES_".

0 Kudos
Reply