Feb 10, 2017 8:11:25 AM by Michael J Swart
Some trivia for your Friday. I recently got a question asking about what the attribute “monitorLoop” is in the blocked-process-report because it’s not really documented.
You won’t find too much explanation about that field in the official documentation, because the schema is considered undocumented. But I believe I know what it means.
The blocked process report is closely tied to deadlock detection and it’s generated by the same process as the deadlock monitor. If you remember, the deadlock monitor runs frequently looking for deadlocks (which are just blocking chains in a circle). It runs every couple seconds when there are no deadlocks, and if it detects any, it runs a bit more frequently. Each time it runs it’s called a monitor loop. The
monitorLoop is just a number that starts at zero when the server restarts and increments by one each time the monitor runs.
For me, when processing a collection of blocked process reports, it’s useful way to group them together so that if several blocked process reports have the same monitor loop, then they were detected at the same time and can be part of the same blocking chain.
This attribute wasn’t always there. There are some old versions of SQL Server that don’t provide the monitorLoop attribute. This is a reminder that the schema of the blocked process report can change (has changed and will change) without notice.
Written by Michael J Swart
Michael is a Senior Database Developer from Waterloo, Ontario, Canada. Michael has worked in the I.T. industry for over ten years and has worked extensively with some Oracle background. He's focused on SQL Server for the past six years.