Submit a ticketCall us

Looking to compare latest NPM features with previous versions of NPM?
The NPM new feature summary offers a comparison of new features and improvements offered with this release.

 

Home > Success Center > Database Performance Analyzer (DPA) > Database Performance Analyzer Getting Started Guide > Investigate performance issues > Investigate an application performance problem

Investigate an application performance problem

Table of contents
No headers
Created by Melanie Boyd, last modified by Melanie Boyd on Sep 29, 2016

Views: 35 Votes: 0 Revisions: 4
DPA_GS_Home.png

The following example shows how DPA can be used to find the root cause of an application performance problem. In this scenario, users are complaining about the performance of an application developed in-house. The performance problems occur around 2 p.m., during core business hours.

  1. From the DPA homepage, 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.

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/020/gs-investigate-top-sql-statements.png

  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 p.m. hour, one SQL statement caused significantly longer waits than all the others.

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/020/gs-investigate-big-bar.png

  3. Hover over that segment in the bar to display additional information about the SQL statement, such as the percentage of total wait time and the average execution time.

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/020/gs-investigate-hover.png

  4. To make this statement easier to identify, name the SQL statement.

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

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/020/gs-investigate-name-sql.png

  5. Click the bar 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.

  6. Click the name of 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.

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/020/gs-investigate-wait-advice.png

  7. To investigate further, click the SQL statement name to view detailed information about this statement. The statistics show that this statement performed a high number of logical reads.

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/020/gs-investigate-sql-details.png

  8. In the upper-right corner, click Run Query Analysis. In the Analyze Query dialog, accept the default time frame and click Run Analysis.

    The Advice section of the analysis shows that a full table scan is being performed and suggests adding an index.

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/020/gs-investigate-analyzer-advice.png

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

  9. Scroll down to the Supporting Data section, which shows that the query is being run by Accounting.

    Rather than add an index immediately, you decide to share the analysis with the Accounting team and ask if they can tune the SQL statement.

  10. Click the mail icon in the upper-right corner and enter your message. Then click Send.

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/020/gs-investigate-analyzer-email.png

    In this scenario, the Accounting department replies that the SQL statement cannot be changed. With this information, you decide to add the index.

  11. After adding the index, add an annotation to let the rest of the team know that a change was made and to help determine whether the change is effective.

The new index improves the SQL statement's execution time, and users no longer complain about slow performance when it runs. But to ensure that this SQL statement doesn't cause problems in the future, you can add an alert to notify you if the average wait time for this statement increases.

Last modified
17:05, 29 Sep 2016

Tags

Classifications

Public