OK, we’ve got notifications. We need SQL Server to give us more information on who is involved in the blocking.
I like to use the built-in Blocked Process Report for this. This has been in SQL Server for a long time, and it’s extremely useful.
The Blocked Process Report shows you the “input buffer” of the commands involved – it may be partial information and not the full text of the query. It will also show you the login name for who is running what, and the type of lock requests involved.
You don’t have to spend extra money to get good diagnostic information, at least about these items.