How to alert for a single query exceeding a specified Total Wait time threshold?

Question

We would like a custom alert that looks for single queries that generate more than a given amount of wait time within a certain time period (for example, 20 minutes of total wait time within 10 minutes). This would allow catching either single long-running queries as well as shorter queries that are being executed with great frequency and potentially generating undue load on the server.

I see slightly different examples for total wait time for the whole server and for long-running queries, but not for this variation. The target platform is SQL Server.

Any thoughts?

jimjo · Answer

We are already using that custom alert. It covers half the potential issue: when a query goes long on a single execution. We are also interested in finding the unanticipated situations where a query is being run repeatedly, and the total wait for that query over a certain timeframe is excessive. When we run into one of these cases, we can set up the "Total SQL Wait Time for a Single Query" alert for that specific item, but we are reacting after the fact.

We were hoping to figure out a way to be "generic" in the alert so we could have DPA proactively spot the case where a previously well-behaved (or infrequent) query begins accumulating excessive wait times. It may not be possible, so the simple answer may be to train the application teams to simply check the DPA dashboard every hour or two to spot unusual activity.

jaminsql · Answer

Something like this one? Custom Alert - Long Running Queries

jimjo · Answer

That works for a known query, but what we are looking for is to monitor for *any* query that crosses a set threshold,