Return
-
Title
Understanding SQL Server Blocking Visibility in Foglight (With and Without SQL PI) -
Description
This article explains how Foglight displays blocking activity in SQL Server databases, both with and without SQL Performance Investigator (PI) enabled. It also clarifies how thresholds and configuration settings affect the "Blocking (Current)" and "Blocking (History)" views.
-
Resolution
Blocking Visibility Without SQL PI Enabled
Blocking (Current) Panel
- The Foglight database agent uses either direct RMI connections (pre-7.0) or callbacks (7.0 and higher) to run SQL queries against the database (outside of SQL PI).
- The "Blocking (Current)" view displays live blocking events regardless of any configured thresholds.
- Since SQL Server can exhibit frequent, short-lived blocking, users may notice perceived spikes in the "Blocking (Current)" panel. This is expected behavior due to the transient nature of blocking.
Blocking (History) Panel
- The minimum duration (in seconds) required for a blocking event to be collected and displayed in "Blocking (History)" is configured in the Agent Administration panel.
- Blocks shorter than this minimum duration will not appear in the history view.
Blocking Visibility With SQL PI Enabled
Collection and Aggregation
- SQL PI collects blocking data every 1 second; however, aggregation of this data occurs every 1 minute.
- This explains why blocking events in the PI dashboards may not appear instantly and may take approximately 30 seconds to become visible.
- The minimum duration setting from the agent Administration panel does not apply when PI is enabled. All detected blocking is collected and displayed.
Display Behavior
- Blocking history is presented per dimension in the PI tree (for example: by program, user, database, etc.).
- This allows users to focus on blocking activity from a specific program or resource.
- At the instance level, there is currently no way to exclude specific blocking events from display. Similarly, entire programs cannot be excluded from instance-level aggregation.
- The PI interface allows users to begin block analysis by starting with the blocked resource, which helps them avoid reviewing unrelated locks (see Screenshot 1 for example).