How to catch blocking information that appears in the future

Question

Is there any way to catch blocking information that appears sometime in the future? Cause we are not sure when the block comes.

Answer 1

Hi @Riley

There are some queries that you could catch information of blocking. You could schedule the script executed periodically.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Catching information about blocking that has not occurred yet? Nah, SQL Server is not that advanced.

But what you can do is run this:

EXEC sp_configure 'blocked process threshold (s)', N
RECONFIGURE

Where N is a number divisible by 5. Say that you set to 20. If SQL Server finds that a blocking situation has been going on for 20 seconds, SQL Server will then generate a blocked-process event. You can then track these events with Trace, Extended Events or Event Notifications. These events information includes information about the blocking situation. What commands the blocker and the blockee is running and which locks the blockee is waiting for.

Answer 3

sometime in the future?

No, really not. You could only check if currently there are locks on resources you what to access, but that would be a short snapshot for that very one millisecond; the effort is greater than the benefit to implement it.

See https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-ver16