Submit a ticketCall us

Systems Monitoring for Dummies
Our new eBook will teach you the fundamentals and help you create monitors and alerts that are effective, meaningful, and actionable. Monitoring is more than a checkbox on your to-do list. This free eBook will give you practical advice to help you succeed in all aspects of monitoring – discovery, alerting, remediation, and troubleshooting. Don’t miss out on this indispensable resource for newbies, experienced IT pros, and everyone in between. Register Now.

Home > Success Center > Database Performance Analyzer (DPA) > DPA 11.1 Administrator Guide > Alerts > Alert on increases in SQL wait times

Alert on increases in SQL wait times

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

Views: 78 Votes: 0 Revisions: 6

Use the steps below to determine the average execution response time of your SQL queries, and then create an alert to warn you of significant deviations.

Determine the average execution time of your queries

  1. On the DPA home page, click a Database Instance.
  2. Select the first query from the View Historical Charts for SQL list.
  3. Scroll down to the Average Wait Time per Execution chart.

    This chart shows the average execution time for the SQL statement on each day. The black bar denotes the average wait for this query across the days in the chart.

  4. Point the cursor to each bar to see details for the day.
  5. Point the cursor to the black bar to see the baseline of average wait time for this query for all executions for the month.

    SolarWinds recommends alerting if the average wait time is twice as long as this baseline.

    For example, if the baseline of average wait time for the month is 1.5 seconds, set the alert to 3 seconds.

Identify the top three queries and estimate the typical wait time. Choose a threshold, approximately twice as long, that you will use to create alerts.

Create an alert based on the wait threshold

  1. Click Alerts > Manage Alerts.
  2. Verify Wait Time is selected as the Alert Category.
  3. Select Average Wait Time for a Single SQL from the Alert Type list.
  4. Click Create Alert.
  5. Name the alert and enter notification text.
  6. Set the Execution Interval to 10 minutes or more. This allows time for valid samples and prevents unnecessary alerts from a single slow execution.
  7. Select an Available Database Instance, and click Add.
  8. Under Alert Parameters, search for one of the three SQL queries you identified earlier.

    SolarWinds DPA always returns the hash value from a search, even when you select a named query.

  9. Under Alert Levels & Notifications, choose an alert level and enter a minimum time in seconds. This value should be twice as long as the baseline of average wait time for this query.

    The average wait time is specific to the execution interval. If the interval is 10 minutes, SolarWinds DPA looks at the average wait time for the SQL statement chosen for that 10-minute period and compares these values to your thresholds.

  10. Select a Notification Group or Contact.
  11. Click Test Alert, and verify you received the email.
  12. Click Save.
  13. Repeat steps 3 - 13 for the other two SQL queries you identified.
Last modified

Tags

Classifications

Public