DBA - How To Find Blocked Processes In SQL Server

Sometime we run a query and expect that to complete quickly but it kept running. Finally we ask DBA what is blocking this query? Blocking happens when a process hold a lock and other process has to wait till the first process completes.

To understand this, First we are going to start a process that is going to hold update lock on a table and then in second process we will try to update the same column with different values.

Fig 1: Starting First Process

We have executed query with Begin Tran , this query is updating CountryName in dbo.Countries table. We have not Commit this transaction.

Open a new query window and run query as shown in Fig 2.

Fig 2: Initiating second update query on dbo.Countries table

You have noticed that the status of second query is Executing Query...  and it is not completing at all. 

Let's check what is blocking our second query.

1-- Use sp_who or sp_who2

We can use sp_who or sp_who2 to see which processes are blocked by other processes. sp_who2 provide little more information such as Program name which initiated query etc.

Fig 3: Find Blocked Processes in SQL Server by using sp_who or sp_who2

2--Use Sysprocesses

The below query can be used to get all blocked processes with blocking SPIDs.

USE MASTER

GO

SELECT spid,
       blocked                   AS BlockingSPID,
       (SELECT CRI.TEXT
        FROM   sysprocesses st
               CROSS apply sys.Dm_exec_sql_text(sql_handle) CRI
        WHERE  spid = s.blocked) AS BlockingQuery,
       PROGRAM_NAME,
       nt_userName,
       loginame,
       DB_NAME(s.dbid)           AS DatabaseName,
       CR.TEXT                   AS Query

FROM   sysprocesses s
       CROSS apply sys.Dm_exec_sql_text(sql_handle) CR

WHERE  blocked <> 0

Fig 4: Using sysprocesses to get blocked and blocking SPIDs in SQL Server

Now we know which process is blocking our process, we can further look into the details of it. If process is stuck and we want to kill it .

We can use Kill SPID. 

Note:Take a detail look before you kill any process.