Alert for Query Timeouts

[bruceasd]

I'm trying to figure out how alerts work. Would it be possible, for example, to have an alert when timeouts occur due to "2 - Abort" as described in the article "Troubleshoot Query Timeouts"? How could I do that? Thanks.

[DavidWiseman]

The easiest option might be to create a SQL agent job against the repository database that fails when you have too many timeouts. e.g.

DECLARE @Threshold INT=1
IF EXISTS(

		SELECT 1
		FROM dbo.SlowQueries
		WHERE Result = '2 - Abort'
		AND timestamp >= CAST(DATEADD(mi,-5,GETUTCDATE()) AS DATETIME2(3)) -- 5min
		--AND InstanceID = ??
		HAVING COUNT(*) >= @Threshold
)
BEGIN
	RAISERROR('Abort queries higher than threshold',11,1)
END

You can then create an alert in DBA Dash for the job failure.

It's an interesting question. I don't think there is a performance counter you can track, but I might be able to add a 'virtual' performance counter for this based on the slow query collection. With a counter to track it, it would be easier to create an alert. I might consider this in a future update.

[DavidWiseman]

This will be available to add as a metric in the next release.

[ruffk]

Hi David

Using the service config app I searched for this counter but I didn't see it available to add. Am I doing it wrong?