Submit a ticketCall us

Training ClassThe Orion® Platform Instructor-led Classes

Provided by SolarWinds® Academy, these trainings will introduce users to the Orion Platform and its features, management, and navigation. These courses are suitable for users looking to discover new tips, tricks, and ways to adapt their Orion products to better suit their monitoring needs:
Deploying the Orion Platform
Configuring Orion views, maps, and accounts
Configuring Orion alerts and reports

Reserve your seat.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > DPA 12.0 feature: Table tuning advisors

DPA 12.0 feature: Table tuning advisors

Last updated: July 27, 2018

Every database has inefficient queries—queries that perform a large number of reads but return a relatively small number of rows. In other words, they 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 query? 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.

TblTuningAdv_Overview.png

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

The Table Tuning Advisor page provides information in two sections:

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. 

TblTuningAdv_Inefficient_SQLs.png

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

TblTuningAdv_Inefficient_SQL_detail.png

callout1.png

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.

callout2.png 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.
callout3.png 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.
callout4.png

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.

TblTuningAdv_TableStats.png

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?

TblTuningAdv_ExistingIndex.png

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

TblTuningAdv_TableColumns.png

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.

    TblTuningAdv_LearnMore.png

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

Tags

Classifications

Public