Investigate an increase in wait time with DPA

The following example shows how DPA can be used to find the root cause of an increase in wait time.

The problem

The wait time for a SQL statement that runs regularly has increased significantly.

From the DPA home page, click the database instance that the SQL statement runs against.
In the Top SQL Statements trend chart legend, click the SQL statement's name or hash value.

The Query Details page shows the wait times for the past 30 days. You can see that the wait time started to increase around April 23.
Scroll down the page to review the charts. Notice that:
- The Statistics chart shows that the Number of Executions is unchanged but Wait Time Per Execution increased significantly around April 23.
- DPA detected a plan change and displayed the Plans chart automatically. The Plans chart shows that the increase in wait time occurred at the same time the plan changed.
On any chart, click the bar that represents April 23 to see more details about that day.

The Top Waits and Statistics charts confirm that the wait times started increasing during the hour that the plan changed. You can also see that charts in the Instance Resource Metrics section don't indicate any resource pressure.

After determining the root cause, you can click the hash values in the Plans section to display each plan and find out what changed.

Learn more about the DPA approach to investigating performance issues. For other examples of using DPA to investigate a performance issue, see: