Why Does "DBSS - Long Lock Running" Alarm Fire Later Than Expected? (4380361)

The "DBSS - Long Lock Running" alarm in Foglight SQL PI is designed to notify when a SQL Server process is blocked for longer than defined thresholds (e.g., Warning at 10s, Critical at 20s, Fatal at 30s). However, users may notice that alarms do not fire precisely at these intervals, and shorter-duration locks (such as 10 or 29 seconds) may not trigger alarms immediately.

The "DBSS - Long Lock Running" alarm notifies administrators when a SQL Server process has been blocked for longer than a user-defined threshold (e.g., 10, 20, or 30 seconds). The SQL PI (Performance Investigator) engine queries SQL Server every second to detect blocking events, using the `engine.olap.dmvRequestsFrequencyMS=1000` setting.

Key Points:

• SQL PI samples lock/blocking data every second. If a blocking event is present during any sampling point, it will be captured.
• If a blocking event is extremely brief (less than one second) and occurs entirely between two samples, it may be missed.
• All blocking events that last at least one second, or overlap a sample, will be detected.

However, the alarm evaluation (when Foglight decides whether to fire an alarm) is based on the aggregation/processing interval, defined by `engine.olap.resolverIntervalMinutes` (minimum 1 minute). This means:

• Alarms are only evaluated (and can only fire) at the end of each processing interval, not instantly when the block is detected.

Example:

• If you set Warning = 10s, Critical = 20s, and Fatal = 30s, and a process is blocked for 31 seconds:
  • The blocking event will be captured by PI (because it overlaps many 1-second samples).
  • However, the first alarm that will be fired could be the Fatal alarm, since all thresholds may be crossed before the end of the 1-minute interval when evaluation occurs.
• If only Warning = 10s is set, an alarm such as "At least one process has been blocked for more than 10.0 seconds." may be generated, but this alarm will not be triggered until up to a minute after the lock actually starts.

This behavior is by design due to the architecture of SQL PI and the resolver interval. To summarize:

• Alarm thresholds (Warning, Critical, Fatal) determine which severity will be triggered based on how long a lock persists.
• Sampling interval (`engine.olap.dmvRequestsFrequencyMS=1000`) is every second: nearly all blocking events longer than 1 second are detected.
• Resolver interval (`engine.olap.resolverIntervalMinutes`) is the processing cycle (minimum 1 minute): alarms are only evaluated and can only fire at the end of each cycle.
• Implications:
  • Short-duration locks (less than one second) may not be detected at all.
  • Locks that cross multiple thresholds within a single processing interval will only trigger the highest-severity alarm first (e.g., Fatal).
  • Lowering thresholds below the resolver interval (e.g., 10s, 20s, 30s) will not make alarms fire sooner than the processing schedule allows.

Example Scenario

With a Warning condition of 10 seconds, Foglight may eventually fire an alarm stating "At least one process has been blocked for more than 10.0 seconds," but the alarm will not be fired until up to a minute after the blocking begins. If you set Critical at 20 seconds and Fatal at 30 seconds, and a block event lasts 31 seconds, the first alarm that will fire is the Fatal alarm.

Best Practice

• Set thresholds understanding that alarm notifications may be delayed by up to the resolver interval (usually 1 minute).
• Sub-minute alarm evaluation is not currently supported; if required, request a product enhancement from Quest.