Use DPA to alert on increases in SQL wait times
Use DPA to determine the average execution response time of your SQL queries, and then create a DPA alert to warn you of significant deviations.
Determine the average execution time of your queries
- On the DPA homepage, click a Database Instance.
- Select the first query from the View Historical Charts for SQL list.
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.
- Point the cursor to each bar to see details for the day.
- 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
- Click Alerts > Manage Alerts.
- Verify Wait Time is selected as the Alert Category.
- Select Average Wait Time for a Single SQL from the Alert Type list.
- Click Create Alert.
- Name the alert and enter notification text.
- Set the Execution Interval to 10 minutes or more. This allows time for valid samples and prevents unnecessary alerts from a single slow execution.
- Select an Available Database Instance, and click Add.
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.
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.
- Select a Notification Group or Contact.
- Click Test Alert, and verify you received the email.
- Click Save.
- Repeat steps 3 - 13 for the other two SQL queries you identified.