Documentation forDatabase Performance Analyzer

Use the Trends charts to view data about wait times for an instance

From the DPA home page, click the name of a database instance to view information about that instance in the DPA Trends charts. The Total Wait chart on the SQL tab is displayed initially.

Examine SQL statements with the highest wait times

The Total Wait chart on the SQL tab identifies the SQL statements with the highest cumulative wait times during the selected time period. In many cases, these SQL statements have the highest performance impact on end users and applications.

By default, the initial time period is the past 30 days of DPA data collection. To change the default for all Trends charts, edit the advanced Support option TOP_INSTANCE_CHART_DAYS.

  • The vertical bars are a wait time summary for each day.

  • The colored slices represent each SQL statement. The size of the slice represents the total time the SQL statement took to run, including all executions during the entire day. Bigger slices mean longer wait times for the users or applications that are waiting for the SQL statement to finish.

    To determine where to focus your tuning efforts, look for SQL statements that are consistently represented by large slices.

    Some long-running SQL statements (for example, maintenance jobs that run outside of business hours) are not candidates for tuning. The slices that represent these SQL statements can sometimes dominate the Trends charts. You can exclude these statements from the charts in order to focus on the waits that affect end users.

  • Hover over a slice to see a summary of the SQL statement's performance that day.

  • The legend on the right lists the SQL statements ordered by cumulative wait time. By default, the legend lists SQL statements by their hash values. You can name a SQL statement to make it easier to identify.

  • A flag above a bar represents an annotation. Hover over or click the flag for more information.

    Annotations are shown on all Total Wait and Average Wait Trends charts.

Use this chart to identify SQL statements causing long waits that could potentially be tuned. When you identify a candidate for tuning, click the SQL statement name or hash in the legend to view in-depth analysis of that statement.

To walk through examples of using DPA to identify SQL statements for tuning, see Examples of investigating performance issues with DPA.

View the average wait times for long-running SQL statements

On the SQL tab, click Average Wait to see the average wait time for the longest running SQL statements.

  • The vertical bars are a wait time summary for each day.
  • The size of the colored slices represent the average wait time of each SQL statement.
  • The legend on the right lists the SQL statements ordered by cumulative wait time (the same order as the Total Wait chart).

Use this chart to determine whether the performance of a SQL statement on a particular day was normal. Look for time periods where a query’s average time was abnormally high, then drill down to see what was happening during that period to cause the anomaly.

To calculate the average wait, DPA takes the SQL statements with the most cumulative wait time over the latest 30 days of DPA data collection. It then divides the daily wait time of each SQL statement by its execution count.

See what time long-running SQL statements typically run

On the SQL tab, click Typical Day to see what time of day the top SQL statements typically run.

  • The vertical bars represent the typical wait time for each hour of the day.
  • The colored slices identify the SQL statements that typically run during each hour. The size of the slice represents the typical wait time for the SQL statement for all executions during that hour.
  • The legend on the right lists the SQL statements ordered by cumulative wait time (the same order as the Total Wait chart).

Use this chart to determine if maintenance jobs are running during core hours and perhaps interfering with application query performance. If you see that SQL from maintenance jobs are bleeding into core business hours:

  • Consider changing the schedule of the maintenance jobs.
  • Consider tuning the SQL for maintenance jobs. Click the SQL hash value or name in the legend to open the Query Detail page and investigate the performance of the query.

You can also use this chart to understand usage patterns for the database. When you monitor your database instance with DPA at all times, you can see when critical applications are in use. Knowing this helps you determine when to schedule maintenance jobs to prevent them from affecting critical application performance.

For additional insight into what applications are running during each hour, click the Programs tab and select Typical Day. Typical Day is available for many of the DPA Trends charts.

To calculate the values on this chart, DPA takes the SQL statements with the most cumulative wait time over the latest 30 days of DPA data collection. For each hour of the day (for example, midnight to 1:00 AM or 1:00 AM to 2:00 AM), it sums the wait for each SQL statement that occurred during that hour (across all 30 days) and divides by 30.

View the Trends charts for other dimensions

See wait time from different perspectives by clicking the other tabs. Use these tabs to get a complete picture of what the SQL statements are doing inside the database (for example, look at the Waits, Files, and Drives tabs) and who or what is generating SQL statements that are causing significant waits (for example, look at the Users, Programs, and Machines tabs).

The available Trends charts vary for different types of database instances. Examples include:

  • The Waits chart shows what type of waits are affecting query performance. Knowing the type of wait causing a performance issue can help you fix the issue. Click a wait type name in the legend to display detailed information about that type of wait, including possible resolutions.

  • The Programs charts shows which programs or applications generated the SQL statements that caused the longest waits. "No Program Name" means that the application did not set a program name in the database connection properties.

  • The Databases chart shows how long users waited for data from each database. "No Database" means that the application did not specify a database context before issuing queries (that is, it did not connect to a specific database or issue a USE command).

  • The Machines chart shows which servers or workstations generated the SQL statements that caused the longest waits. In load-balanced environments, verify that the load is distributed correctly.

  • The DB Users chart shows which users generated the SQL statements that caused the longest waits.

  • The Plans tab shows the cached plans that had the most active wait time. Hover over a slice for details. Click a plan hash in the legend to display the plan text (and advice if available) from the database vendor. Examine the plan text to see which steps or operations have the highest cost. Click Download to download the plan.

Drill in to a specific day or time period

If users complain about slow performance on a specific day or time, drill in to the Trends charts to find out more about what was happening during that period. On any Trends chart tab:

  • From the 30-day chart, click a bar on the chart or a label on the x-axis to see the one-day chart. Each bar on the one-day chart represents a one-hour interval.

  • From the one-day chart, click a bar on the chart to see the data for that hour. Each bar on the chart represents one entity, depending on the type of Trends chart. (For example, on the SQL chart, each bar represents one SQL statement. On the Programs chart, each bar represents one program.) The bars are color-coded by the wait type.

  • From the one-hour chart, click the identifier to the left of a bar for more information about that entity. For example, on the SQL chart, click the SQL hash or name to view in-depth analysis of that statement. On the Waits chart, click the name of a wait type to see a list of SQL statements that ran during that period with that type of wait.

  • When you drill down to a one-hour chart, the Timeslice tab becomes available. Click this tab to see the SQL statements that ran during each 10-minute interval within the hour. Use the Interval drop-down to change the length of the intervals.

  • When you drill down to a one-hour chart, you can choose a SQL statement from the Filter on this Query drop-down to view SQL data for that SQL statement.

    The available options depend on the type of monitored database instance. For example, on a MySQL or PostgreSQL instance, you can generate the execution plan.

View related data

Scroll down and click the Resources tab to view resource charts for the selected time period. For some database types, Blocking and Deadlocks tabs are also available. These charts can help you determine if resource contention, blocking, or deadlocks affected SQL performance.

Email a chart

When a chart illustrates a performance problem (or a tuning success), you can share it with non-DPA users by emailing it to them.

  1. Click the email icon in the upper-right corner.

  2. Enter the recipient, subject, and message.

  3. Select the options, and click Send.