Blocking is completely normal in SQL Server. You will typically be unaware of the many short blocks happening all the time.
Sometimes, blocks take longer than expected to resolve. Blocks of longer duration can create chains, where a blocked process blocks additional processes and so on. This type of blocking scenario is problematic.
The concern is not with blocking, but rather excessive blocking.
Video links below.
*What is Locking in SQL Server
[ Ссылка ]
*Locking Resources in SQL Server
[ Ссылка ]
*Locking modes in SQL Server
[ Ссылка ]
*Microsoft Documentation on Blocking
[ Ссылка ]
*sp_Whoisactive documentation link(to download stored procedure)
[ Ссылка ]
--T-SQL scripts used in this video
**UPDATE in spid 52
begin tran
update table1
set Id = 6201
where Ext = 122
**in spid 54
select * from table1
select request_session_id, request_mode, request_type,
resource_type, resource_description
from sys.dm_tran_locks
select session_id, wait_duration_ms, wait_type,
blocking_session_id, resource_description
from sys.dm_os_waiting_tasks
select session_id, status, wait_time, wait_type, wait_resource, command
from sys.dm_exec_requests where
select * from sys.sysprocesses
sp_whoisactive @get_locks = 1
dbcc inputbuffer(52)
sp_who2 active
kill 52
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'blocked process threshold', 5
RECONFIGURE;
