Create a DPA alert to monitor SQL statement execution time
Use DPA alerts to send notifications about potential problems so that your team can take steps to resolve the issue before it affects end users.
A previous example showed how to use DPA to resolve performance problems that were caused by a long-running SQL statement. This example adds an alert to notify the DBA group if the average execution time of that SQL statement is above the specified threshold. If this alert is triggered, the team can stop the query before end users notice a performance problem.
- On the DPA menu, click Alerts.
- Click the Manage Alerts tab.
Select the alert category and the alert type.
For this example, select Wait Time as the category and Average Wait Time for a Single SQL as the type.
- Click Create Alert.
Enter a unique name, select the execution interval, and enter the email notification text.
SolarWinds recommends an execution interval of at least 10 minutes. This allows time for valid samples and prevents unnecessary alerts from a single slow execution.
Select the database instance that the SQL statement runs against.
Select the SQL statement the alert monitors.
Click Search next to the SQL Hash field.
Because this SQL statement has a name, leave Name and Hash selected as the SQL Search Type.
Select the name of the SQL statement from the list.
- Click OK. The hash value of the SQL statement is displayed in the SQL Hash field.
Specify the thresholds for each alert level you want to enable.
In this example, a High alert is triggered if the average wait time of the SQL statement during an execution interval is 30 seconds or more.
Select the person or group who gets notified when an alert level is triggered and when the alert is broken. (The alert status is set to Broken if an error occurs during execution.)
If you have not added the person or group as a contact in DPA, click Add Contact or Add Contact Group. See Create contacts and contact groups in the DPA Administrator Guide.
- Click Test Alert and verify that the recipients receive the email.
- Click Save.