Introduction

Use the blocked process threshold option to specify the threshold, in seconds, at which blocked process reports are generated. The threshold can be set from 5 to 86,400. The lock monitor only wakes up every 5 seconds to detect blocking conditions (it’s also looking for other conditions such as deadlocks). Therefore, if you set a blocked process threshold value to 1, it doesn’t detect a process that has been blocking for 1 second. The minimum time it can detect a blocked process is 5 seconds.

Problem

Enable Blocked Process Report

Solution

https://github.com/steverezhener/DBA/blob/master/Procedures/bestpractices.usp_change_server_blocked_process_threshold.sql

Based on

https://www.sqlskills.com/blogs/erin/capture-blocking-information-with-extended-events-and-the-blocked-process-report/