Find Blocking and troubleshoot

What is Blocking:

One process is already acquired the locks on a set of rows and other process tries to acquire a lock on the same set of rows. Due to this other process has to keep waiting until first process releases the lock by finishing its job.

Find blocking:

We can use sp_lock, sp_who and sp_who2 system stored procedures to find the blocking session. After getting the SPID, we can user DBCC INPUTBUFFER (SPID) to find the SQL statement that is causing blocking. The returned SQL statement will not be complete SQL statement instead ii will have first 128 characters only.

To see the complete SQL text we can use dynamic management view sys.dm_exec_session and dynamic management function sys.dm_exec_sql_text(sql_handle).

To resolve blocking, you can disconnect the connection causing the blocking using KILL command. If this does not solve the problem permanently, then rewrite the stored procedure causing the block more efficiently.