There are 250 blocking sessions on my instance. Please advise how to resolve this.
There are 250 blocking sessions on my instance. Please advise how to resolve this.
Your question make totally no sense.
How can we advise you anything without any information?!?
having block can be normal in one system and an issue in another.
It is up to you to monitor what block what and after you have the information we might be able to help you avoid some of the blocks by changing your queries or the way you execute them or changing the system design to avoid the situation that led to the blocking.
These type of question: "Please help me I have an issue" make no sense without more information
The best advice is to start monitoring your system => check the answers you got on how to do so
Hi @ChaitanyaKiranBuduguru-7986, have you resolved your issue? any update for this?
There is no blocking now. Thanks a lot for your help.
Hi @ChaitanyaKiranBuduguru-7986,
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.
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;
15 people are following this question.