What is Blocking

Blocking occurs when one or more sessions request a lock on a same resource, such as a row, page, or table.

when one connection holds a lock and another second connection also requires a lock that case second connection will to be blocked until the first connection complete.

How can find Blocking: Its have many options like

• System Stored Procedure
  • Exe sp_who2
• DMVs (Dynamic management views):
  • sys.dm_exec_requests  
  • Sys.dm_os_waiting_tasks
• SQL Server Management Studio
  • Activity Monitor
  • Reports
• SQL Server Profiler

1. sp_who2 :

sp_who2 is popular system stored procedure and SSQL server’s all connection’s information provided.  like session id, users, application, commands, status, database, CPU time, etc.

Below is some sample code T56 selecting same huge data and T57 want to update same table means T56 have shared (S) lock and T57 want exclusive (X) lock so T57 need to wait until T56 complete so its blocked  a screen shot with showing T57 being blocked by T56 and T57’s command is Update.

2. sys.dm_exec_requests:

its also provide all sql server’s connection with more useful  columns like   

SELECT session_id,blocking_session_id,status,command,wait_type,wait_resource,start_time

FROM sys.dm_exec_requests

WHERE blocking_session_id <> 0;

3. dm_os_waiting_tasks:

returns information about the tasks that are waiting on resources.

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id

FROM sys.dm_os_waiting_tasks WHERE blocking_session_id <> 0

4. Activity Monitor:

its graphical tool for monitor process, resources, expensive query etc. like

5. Reports:

Blacking Transaction Report is also part of Management studio.and SSMS provided so many types of reports like

6. Profile Events:

Blocked process event we can select its provided by profiler under Errors and Warnings events like