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 option 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.

The post Identifying Blocking Chain in SQL Server appeared first on .