250 Blocking sessions

Question

There are 250 blocking sessions on my instance. Please advise how to resolve this.

Answer 1

Hi @Chaitanya Kiran Buduguru ,

Welcome to Microsoft Q&A!

You could use below script to get the lead blocking session, and you could know who is waiting for whom. Please refer to SQL SERVER – Identifying Blocking Chain Using SQL Scripts to get more information:

SET NOCOUNT ON  
GO  
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH  
INTO #T  
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T  
GO  
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)  
AS  
(  
SELECT SPID,  
BLOCKED,  
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,  
BATCH FROM #T R  
WHERE (BLOCKED = 0 OR BLOCKED = SPID)  
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)  
UNION ALL  
SELECT R.SPID,  
R.BLOCKED,  
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,  
R.BATCH FROM #T AS R  
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID  
)  
SELECT N'    ' + REPLICATE (N'|         ', LEN (LEVEL)/4 - 1) +  
CASE WHEN (LEN(LEVEL)/4 - 1) = 0  
THEN 'HEAD -  '  
ELSE '|------  ' END  
+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE  
FROM BLOCKERS ORDER BY LEVEL ASC  
GO  
DROP TABLE #T  
GO  

After you find the blocking session, you need to analysis it. Please refer to Understand and resolve SQL Server blocking problems to get more information.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

We don't have access to your SQL Server to analyse it, so you have to investigate on your own.
First check currently running processes and whow is blocking who.

-- Current processes and their SQL statements
SELECT PRO.loginame AS LoginName
      ,DB.name AS DatabaseName
      ,PRO.[status] as ProcessStatus
      ,PRO.cmd AS Command
      ,PRO.last_batch AS LastBatch
      ,PRO.cpu AS Cpu
      ,PRO.physical_io AS PhysicalIo
      ,SES.row_count AS [RowCount]
      ,STM.[text] AS SQLStatement
     -- ,PRO.program_name
      ,PRO.spid
      ,PRO.blocked, PRO.hostname, PRO.open_tran
FROM sys.sysprocesses AS PRO
     INNER JOIN sys.databases AS DB
         ON PRO.dbid = DB.database_id
     INNER JOIN sys.dm_exec_sessions AS SES
        ON PRO.spid = SES.session_id
     CROSS APPLY sys.dm_exec_sql_text(PRO.sql_handle) AS STM     
WHERE PRO.spid >= 50  -- Exclude system processes
      AND PRO.[status] <> 'sleeping'
      AND PRO.spid <> @@SPID 
      or blocked <> 0
ORDER BY pro.blocked, PRO.physical_io DESC
        ,PRO.cpu DESC;