What is a Blocking in SQL Server | Find blocking and troubleshooting steps | SQL Interview Q&A

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 in SQL Server | with DEMO | W...  

*Locking Resources in SQL Server
   • Locking Resources in SQL Server | Loc...  

*Locking modes in SQL Server
   • What are the different locking modes ...  

*Microsoft Documentation on Blocking
https://learn.microsoft.com/en-us/tro...

*sp_Whoisactive documentation link(to download stored procedure)
https://github.com/amachanic/sp_whois...

--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;