Applications Systems

DPA 12.0 feature: Table tuning advisors

Inefficient SQL statements perform a large number of reads to process a relatively small number of rows. But what is the best way to fix the problem? To help you find an answer, the Table Tuning Advisor page displays aggregated information about each table and the inefficient SQL statements that ran against it. Use this information to make informed decisions about performance optimization opportunities, and to weigh the potential costs and benefits of adding an index.

First published date

11/1/2018 1:08 PM

Last published date

6/28/2019 10:45 PM

Overview

Every database has inefficient queries—queries that perform a large number of reads but return a relatively small number of rows. In other words, these queries do a lot of work for a small return. This type of inefficiency results in higher I/O, longer wait times, greater amounts of blocking, and increased resource contention.

But what is the best way to fix the problem? Should you tune the SQL? Add a new index? Or maybe add columns to an existing index? 

To help you answer these questions, DPA 12.0 introduces table tuning advisors. At the end of each day, DPA runs an analysis to identify tables that had inefficient queries run against them during that day. For each table, the Table Tuning Advisor page displays aggregated information about the table and the inefficient queries. Use this information to make informed decisions about performance optimization opportunities, and to weigh the potential costs and benefits of adding an index.

This feature is available for Oracle, SQL Server (2008 and above), and Azure SQL databases.

    Product section

    Database Performance Analyzer

    Resolution

    The Table Tuning Advisor page provides information in two sections:

    • Information about the inefficient queries

    • Information about the table and existing indexes

    Information about inefficient queries  

    The top-left pane lists the inefficient queries that ran against the table on the selected day. DPA assigns a relative efficiency score to each query and uses this score sort the list, placing the least efficient query first. 

    Select a query to display information about it in the top-right pane:

    The bar at the top provides links to the SQL text and the Query Detail page for this SQL statement.  The statistics show the query's wait time and the degree of inefficiency.

    The Reads per Row ratio is the number of rows the query reads for each row it returns. Statements with the highest Reads per Row ratios could potentially benefit most from tuning.

    The Table Tuning Advisor page includes a section for each execution plan that DPA discovers. The section heading provides a link to the entire execution plan, but DPA pulls out the steps that are likely to need the most attention.

    For SQL Server and Azure database instances, the Table Tuning Advisor page displays index recommendations from SQL Server. The Projected Impact is the cost reduction that the optimizer estimates the recommended index will have. Click Show index DDL to see the CREATE INDEX statement for the recommendation.

    DPA analyzes the plan and displays information about the most inefficient steps, including:

    • The step number and the type of operation being performed in the step (for example, INDEX SCAN). Click this line to get detailed information and tuning recommendations for this type of operation.

    • The index this step uses, if it uses an index.

    • Predicates, which are snippets of the query that contain column comparisons that are driving high read workload, and are candidates for indexing.

    • Warnings (if any) that apply to this step. Click the warning for a detailed description of the condition that DPA has identified as a potential reason for concern.

    • The number of rows the optimizer estimates this step will read. Critical and warning icons identify steps that read a high percentage of the table or index rows, and therefore have a greater need for tuning or an index.

    Information about the table and existing indexes  

    Under Current Table Information, consider the Size, Rows, and Average Data Churn values to help you determine whether to add an index:

    • Size and Rows: For large tables, indexes are often critical for good query performance. For small tables, full table scans are sometimes more efficient than indexes.

    • Average Data Churn: The table's churn is the daily number of insert and delete operations as a percentage of the total number of table rows. If the table has high churn and a large number of rows, an index on the table will undergo a great deal of maintenance when insert and delete statements are run.

    Before you decide to add an index, use the Existing Indexes section to review the current indexes. For example:

    • Look at the Fragmentation value to determine if defragmenting an existing index could improve performance.

    • Look at the Stats generated time. For tables with high churn, make sure that each index's statistics are up-to-date so that the optimizer has accurate information when it develops execution plans.

    • If an inefficient query should be using an existing index but isn't, can you adjust the query?

    • Can you add columns to an existing index to make it a "covering index" for the query?

    For your convenience, the Table Tuning Advisor page also displays the structure of the table.

    Learn more 

    • See Investigate inefficient queries running against a table in the DPA admin guide to get tips for using the table tuning advise. 

    • Click the Learn More menu at the top of the Table Tuning Advisor page to find detailed information about how to use the information on this page, including factors to consider when adding an index and advice for correcting common indexing problems.

    • For information about all of the new features in DPA 12.0, see the DPA 12.0 release notes .