Submit a ticketCall us

WebinarUpcoming Webinar: How Help Desk and Remote Support Pays for Itself

Learn how help desk software can simplify ticketing management, allow you to track hardware and software assets, and accelerate the speed of IT support and service delivery. Gain insights on how remote support tools allow your IT team to maximize their efficiency and ticket resolution by expediting desktop troubleshooting, ultimately helping keep end-users happy and productive.

Register here.

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

Investigate an application performance problem

DPA_GS_Home.png

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 p.m., during core business hours.

The investigation

  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

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

    2. In the SQL Properties dialog, enter the name and click OK.

  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.

After adding 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 SQL statement's execution time, and users no longer complain about slow performance when it runs.

Next steps

To make sure 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

Tags

Classifications

Public