Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

Home > Success Center > Database Performance Analyzer (DPA) > DPA 11.0 Administrator Guide > DPA solutions > Determine wait bottlenecks that delay response time

Determine wait bottlenecks that delay response time

Created by Anthony.Rinaldi_ret, last modified by Anthony.Rinaldi_ret on Sep 15, 2016

Views: 34 Votes: 0 Revisions: 6

After you identify the problematic SQL queries based on response time analysis, you can drill down for details about the problem and identify the cause of the bottleneck.

  1. Locate the Instances with Highest Wait Time resource on the home screen.
  2. Pick a day, and click the bar that represents the instance with the highest wait time.

    The daily wait time chart loads, which shows the response time delay in each hour of the day and the SQL queries that created the delay.

  3. Select a value from the Interval list to adjust the time interval.

    File:Success_Center/New_Articles/DPA-110-MT/0L0/top-sql-statements-interval-3-hours.png

  4. Is there an hour that had problems or that represents commonly found problems? Click an hour, such as 9PM above, for more information.
  5. This view ranks the query bottlenecks in the chosen period. The SQL queries with the most accumulated wait time across all sessions are charted to show the total delay.

    File:Success_Center/New_Articles/DPA-110-MT/0L0/top-sql-statements-9-to-12.png

  6. Click a name or hash on the left to see details on the SQL query.

    File:Success_Center/New_Articles/DPA-110-MT/0L0/sql-query-details.png

  7. Click the Programs tab to see which programs were executing that query.
  8. Note the program name in the SolarWinds DPA worksheet.

Identify Waits for the top SQL queries

Now that you have detailed visibility to the problematic SQL query, you can identify the cause of the bottleneck.

  1. Use the breadcrumb trail in the top banner to go to the previous screens.

    File:Success_Center/New_Articles/DPA-110-MT/0L0/breadcrumb.png

  2. Click the time interval to return to the Top SQL Statements for that period.
  3. For the first query, note how the majority of the bar corresponds to a Wait called db file sequential read. This is the item causing delay in this example.

    File:Success_Center/New_Articles/DPA-110-MT/0L0/wait-type.png

  4. Click the Wait in the legend on the right to see an explanation of the bottleneck and solutions.
  5. SolarWinds DPA isolated the top SQL queries creating increased user response time and displayed the Waits responsible for the response time in each one.
  6. For the three SQL queries, identify the two or three Waits that comprise more than 75% of the total response time. Enter this information into the DPA worksheet.

    In many cases, one wait bottleneck is responsible for most of the delay.

Last modified
14:02, 15 Sep 2016

Tags

Classifications

Public