Find Blocking queries in Sql Server

Hello folks, today I will explain regarding most common problem of blocking in Sql Server. There are so many developers having same question “How can we find blocking queries in Sql Server”.

Let’s try to take an example and understand how to find blocking queries in Sql server, find below script:

CREATE TABLE [dbo].[Employees](
	[EmpID] [int] NOT NULL,
	[EmpName] [varchar](100) NOT NULL,
	[HireDate] [date] NULL,
	[DOJ] [date] NULL
) 
GO
INSERT INTO Employees 
SELECT 1,'E1',GETDATE()-10,GETDATE()
GO
INSERT INTO Employees 
SELECT 2,'E2',GETDATE()-10,GETDATE()

Output:

Now let’s try to insert new record by using transaction and we will not close that transaction as shown below code

BEGIN TRAN

INSERT INTO Employees 
SELECT 3,'E3',GETDATE()-10,GETDATE()

--END TRAN

Same time suppose some of user trying to access same table, user will have to wait until transaction will not get completed.

Now try to find blocking query by using below script:

--------------******* Find Blocking in sql server query Starts******  -----------
SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO
--------------******* Find Blocking in sql server query Ends******  -----------

Output:

You could also take reference of whoIsActive script to get in depth fact findings. You can download from http://whoisactive.com/

Output:

Hope you have understood how to find blocking queries in Sql Server.