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 | Why do...
*Locking Resources in SQL Server
• Locking Resources in SQL Server | Lock typ...
*Locking modes in SQL Server
• What are the different locking modes prese...
*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;