HAPPY NEW YEAR 2017!!
I hope you all had a good Christmas and New Year. I always try to review my skills as a DBA and explore different options to make my job easier. Recently, I was working on the blocking issue of SQL Server and found that there were a lot of sessions which were being blocked. Sometimes, If you have multiple blocking processes, it becomes tough to segregate what all the blocking processes (root blocker processes) and blocked processes (victim processes).
If you deal with blocking issue very closely, you will find that you have a node for the blocked process. It means the blocked process is being blocked by another node which we call blocking process or root blocker. This blogs will not only help you to segregate blocked and blocking processes but also allows to capture quite useful information to troubleshooting blocking issue.
SQL Server Blocking
In any relational database platform that uses lock-based concurrency; when it is entertaining many concurrent transactions, there is a high possibility that conflicts will occur because different processes request to access to the same resources with different level of locks at the same time. When the locks are held for a longer time, the second SPID must wait. When these locks are finally released, the second SPID can then obtain its own particular locks on the resource to continue processing. The blocking can cause tremendously poor performance of SQL Server.
Query to Capture Blocked and Blocking Processes
-- List down all the blocking process or root blockers
SELECT DISTINCT p1.spid AS [Blocking/Root Blocker SPID]
, p1.[loginame] AS [RootBlocker_Login]
, st.text AS [SQL Query Text]
, p1.[CPU]
, p1.[Physical_IO]
, DB_NAME(p1.[dbid]) AS DBName
, p1.[Program_name]
, p1.[HostName]
, p1.[Status]
, p1.[CMD]
, p1.[Blocked]
, p1.[ECID] AS [ExecutionContextID]
FROM sys.sysprocesses p1
INNER JOIN sys.sysprocesses p2 ON p1.spid = p2.blocked AND p1.ecid = p2.ecid
CROSS APPLY sys.dm_exec_sql_text(p1.sql_handle) st
WHERE p1.blocked = 0
ORDER BY p1.spid, p1.ecid
-- List Down all the blocked processes
SELECT p2.spid AS 'Blocked SPID'
, p2.blocked AS 'Blocking/Root Blocker SPID'
, p2.[loginame] AS [BlockedSPID_Login]
, st.text AS [SQL Query Text]
, p2.[CPU]
, p2.[Physical_IO]
, DB_NAME(p2.[dbid]) AS DBName
, p2.[Program_name]
, p2.[HostName]
, p2.[Status]
, p2.[CMD]
, p2.ECID AS [ExecutionContextID]
FROM sys.sysprocesses p1
INNER JOIN sys.sysprocesses p2 ON p1.spid = p2.blocked AND p1.ecid = p2.ecid
CROSS APPLY sys.dm_exec_sql_text(p1.sql_handle) st
Here is the output of the query
Click on the image to zoom it
In the above image, the first section of output shows what all the SPIDs are acting blocking process or root blocker and the second part of the output shows what the root blockers are blocking all the SPIDs.
Hope, you enjoyed learning how to get blocking details by segregating blocked and blocking processes.
6 thoughts on “Identifying Blocking Chain in SQL Server”
Hi Dharmendra,
Daily I’m fallowing your articles and learning a lot …:)
Thanks Murali for following my website! I am truly happy as I am able to contribute a bit to your learning.
Hi dharmendra,
Very nice please share sql installation on linux
Thanks Mahendra! You can follow the below link;
http://www.dharmendrakeshari.com/category/sql-linux/
good article
Hi Dharmendra
Good information on blockings, Could you please share Always on related information and videos from basic.