Description

This custom Ignite alert will look for time waiting on any of the SQL Server lock wait type (start with LCK) in the Ignite repository and return the total time waited. This is executed against the Ignite repository while another one listed in the Custom Alerts will go against the monitored instance.

SQL statement

SELECT ISNULL(SUM(sw.qp)/100,0) timesecs
 FROM consw_#DBID# sw
 INNER JOIN conev_#DBID# ev ON sw.keeq = ev.id
 WHERE sw.d >= DATEADD(n, -#FREQUENCY#, CURRENT_TIMESTAMP)
 AND ev.name LIKE 'LCK%'

Ignite alert definition