Find blocking by T-SQL Queries

Blocking is an unavoidable and by-design characteristic of any relational database management system (RDBMS) with lock-based concurrency. As mentioned previously, in SQL Server, blocking occurs when one session holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for which the first SPID locks the resource is small. When the owning session releases the lock, the second connection is then free to acquire its own lock on the resource and continue processing. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance. Source: https://docs.microsoft.com

DMV ‘sys.dm_exec_requests’  provides details on all of the processes running in SQL Server. Its returns information about each request that is executing in SQL Server. Use any one script out of three.

-- Returns information about each request that is executing in SQL Server
SELECT *
FROM sys.dm_exec_requests
GO

-- Returns information about the request that is executing in SQL Server where session_id is greater than 50
SELECT *
FROM sys.dm_exec_requests
WHERE session_id > 50
GO

-- Returns information about the request that is executing in SQL Server.
-- SQL Handle: It is a hash of the SQL Text containing all of your formatting (casing, spaces, etc).
SELECT *
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(sql_handle) GO

Here is another script where the result will be below the table.

SELECT
qs.Session_ID,
Blocking_Session_ID, qs.Status,
Wait_Type, Wait_Time, Wait_Resource, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate() SnapshotDateTime,
--Open_Transaction_Count, 
ss.PROGRAM_NAME, ss.HOST_NAME, ss.Login_Name

FROM sys.dm_exec_requests AS qs INNER JOIN sys.dm_exec_sessions ss
ON qs.session_id = ss.session_id
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE Wait_Time > 0
ORDER BY Wait_Time DESC 

Kill blocking process

1) Use an activity monitor to kill the process

Right-click on the SQL instance name -> Activity monitor -> Expand the process section -> Find the relevant process id and right-click to kill the process.

2) Run the kill command to kill the process

KILL PROCESS_ID

“Please let us know if there are any changes/modifications required. Feel free to comment below – We always appreciate hearing your tips.”