Introduction
DPA can currently alert on resource metrics that exceed specific threshold values. However, some customers have wanted DPA to not only alert based on these threshold but then return more information regarding the actual query workload. For example, if my "Instance CPU Utilization" average goes beyond a certain threshold also return the top 10 CPU consuming queries that were running at that time.
Adjusting the Alert Script
This script was written to look at "Instance CPU utilization" but can easily be modified to look at other resource metrics DPA is collecting. #DBID# and #FREQUENCY# are dynamically populated by via the instances assigned to this alert and the interval time period selected. The Critical Threshold defined in the SQL is the initial value that triggers the alert and the alert thresholds are used to determine critical and warning values for the returned data set. In this example, CPU/Memory in milliseconds of each query. Since we are only returning the top 10 queries, we've set the critical value to 1 which ensures that we get a critical alert status when triggered.
DECLARE @InstanceID varchar(50) = #DBID#;
DECLARE @MetricName varchar(50) = 'Instance CPU Utilization';
DECLARE @CriticalThreshold varchar(5) = 90;
DECLARE @TimeframeMinutes varchar(5) = -#FREQUENCY#;
DECLARE @AvgCPU int;
DECLARE @SQL nvarchar(2000);
Configuring the DPA Alert
To add this alert to DPA, create a custom alert of type Custom SQL Alert - Multiple Numeric Return and make it look similar to this:
