Version 3

    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.

    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.

     

    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.

     

    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.

     

    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.

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