Documentation forDatabase Performance Analyzer

Investigate an application performance problem with DPA

The following example shows how DPA can be used to find the root cause of an application performance problem.

The problem

Users are complaining about the performance of an application developed in-house. The performance problems always occur around 2 PM, during core business hours.

The investigation

  1. From the DPA home page, click the database instance that the application runs against.

    The Top SQL Statements trend chart shows the 15 SQL statements with the highest wait times for the past 31 days.

  2. Click a bar that represents a day when users experienced slow performance.

    The chart shows the top SQL statements for each hour. During the 2 PM hour, one SQL statement caused significantly longer waits than all the others.

  3. Point to that segment in the bar to display additional information about the SQL statement. Note the SQL hash.

  4. In the chart legend, click the hash value that represents the SQL statement.

    The Query Detail page displays DPA's analysis of the query performance, including the types of waits, query advisors, and the most relevant charts. By default, the time period is the same as the period that you selected on the trends chart.

  5. To make this statement easier to identify in charts and reports, name the SQL statement:

    1. In the upper-right corner, click SQL Properties.
    2. In the SQL Properties dialog, enter the name and click OK.

    Legends and reports now identify the SQL statement by name instead of by hash value.

  6. Look at the Top Waits chart to see what type of waits are causing delays.

    The chart shows that this SQL statement spends most of its time in Memory/CPU waits.

  7. Click the information icon next to the wait type in the legend to see more information about Memory/CPU waits.

    DPA provides detailed information about this wait type, including possible solutions.

  8. In the Query Advisors section, notice that DPA's analysis shows that a full table scan is being performed and suggests adding an index.

    You can click the full table scan link for details, or click the plan hash on the right to view the plan.

    Before you add the index, you decide to find out more about where this query comes from.

  9. Click the Supporting Data tab, which shows that the query is being run by Accounting.

Rather than add an index immediately, you decide to first contact the Accounting team and ask if they can tune the SQL statement. In this scenario, the Accounting department replies that the SQL statement cannot be changed. With this information, you decide to add the index.

After you add the index, you can add an annotation to let the rest of the team know what changed and to help determine whether the change is effective.

The new index improves the execution time of the SQL statement, and users no longer complain about slow performance when it runs.

Next steps