Example of investigating 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. In addition, it provides examples of creating an alert that is triggered if the problem recurs and a report to track tuning efforts.

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

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.
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.
Point to that segment in the bar to display additional information about the SQL statement. Note the SQL hash.
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.
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.
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.
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.
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.
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.

Create an alert to monitor SQL statement execution time

To make sure this SQL statement doesn't cause problems in the future, you can add an alert to notify the DBA group if the average execution time of that SQL statement is above the specified threshold. If this alert is triggered, the team can stop the query before end users notice a performance problem.

On the DPA menu, click Alerts.
Click the Manage Alerts tab.
For the Alert Category, select Wait Time.
For the Alert Type, select Average Wait Time for a Single SQL.
Click Create Alert.
Enter a unique name, select the execution interval, and enter the email notification text.

SolarWinds recommends an execution interval of at least 10 minutes. This allows time for valid samples and prevents unnecessary alerts from a single slow execution.
Select the database instance that the SQL statement runs against.
Select the SQL statement the alert monitors.
1. Click Search next to the SQL Hash field.
2. Because this SQL statement has a name, leave Name and Hash selected as the SQL Search Type.
3. Select the name of the SQL statement from the list.
4. Click OK. The hash value of the SQL statement is displayed in the SQL Hash field.
Specify the thresholds for each alert level you want to enable.

In this example, a High alert is triggered if the average wait time of the SQL statement during an execution interval is 30 seconds or more.
Select the person or group who gets notified when an alert level is triggered and when the alert is broken. (The alert status is set to Broken if an error occurs during execution.)

If you have not added the person or group as a contact in DPA, click Add Contact or Add Contact Group. See Define contacts for DPA alert notifications and reports.
Click Test Alert to verify that no errors occur when the alert is executed.
Click Save.

Create a report to track wait types for a single SQL statement

You can create a report to track wait times of the SQL statement and determine if your tuning efforts were effective.

On the DPA menu, click Reports.
Select the database instance and the report type.

For this example, choose Top Waits for single SQL.
Click Search and locate the SQL statement.

If you named the statement, enter part of the name and make sure Name and Hash is selected. Then click Search.

The Search Results lists all SQL statements run against the selected database instance that match your criteria.
Select the SQL statement, and click OK.
Click Report Options.

This report can show the top waits, or it can show specific wait types (regardless of their wait times).
For this example, complete the following steps to specify db_file_scattered read waits and Memory/CPU waits.
1. Under Waits to Display, select User-Defined Waits and click Add.
2. Enter part of the wait name (for example, scattered), and click Search.
  
  The Search Results section lists all waits that match your search criteria.
3. Select db file scattered read, and click OK.
4. Repeat the previous steps to add Memory/CPU waits, and click OK.
Under Dates to Display, specify the dates that the report should include.

In this example, the date range is the current month, and the report includes only weekdays.

The Data Range at the bottom of this section shows the time period for which data is available.

Under General, complete the following fields.

Report Name	Enter a unique name to identify this report in the report list.
Report Title	(Optional) Enter a title to display at the top of the report. If you leave this field blank, the report title defaults to the report type, database instance, and time period.
Report Description	(Optional) Enter a description to explain the report's content or purpose.

In the New Report section at the top of the window, click Display Report.

The report opens. In this example, the drop in wait times after September 7 shows that the tuning efforts were effective.
Click Save and then OK to save the report.

You can view the report from the Reports tab at any time, or schedule the report to run automatically and be emailed to a group of recipients.

Other walk-throughs

For other examples of using DPA to investigate a performance issue, see:

Search SolarWinds Support