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.