Description
This alert will look for time waiting on any of the SQL Server lock wait type (starts with LCK) in the Ignite repository and return the total time waited. This is executed against the Ignite repository while others listed here execute against the monitored instance.
Alert Definition
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert with values similar to these:
Alert Name: Monitor the Ignite Monitors
Execution Interval: 10 minutes
Notification Text: The following Ignite monitors are currently down and not collecting data.
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%'
Execute Against: Repository
Units: Seconds of Blocking Wait
High Threshold: Min 200, Max empty
Medium Threshold: Min 100, Max 200
