In all version of SQL Server we face Locking and Blocking problems. Now its very first thing to know blocking and blocked SPID. When a user or DBA itself come to know that particular process seems to hung or a process is not processing properly, only checking the applicable database blocking makes a great deal to winning the battle. Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock. These forces the second connection to be blocked until the first connection completes. With this being said, locking is a natural occurrence in SQL Server in order to maintain data integrity.
In SQL Server, there are many ways to findout the blocking and blocked SPID/Process. Some options are like:
-
sp_who2 System Stored Procedure
-
sys.dm_exec_requests DMV
-
Sys.dm_os_waiting_tasks DMV
-
SQL Server Management Studio Activity Monitor
-
SQL Server Management Studio Reports
-
SQL Server Profiler
WikiDBA 