question

ChaitanyaKiranBuduguru-7986 avatar image
0 Votes"
ChaitanyaKiranBuduguru-7986 asked CarrinWu-MSFT commented

250 Blocking sessions

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

sql-server-general
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Restart the instance?

0 Votes 0 ·

No Erland. Its a production server.

0 Votes 0 ·
pituach avatar image pituach ChaitanyaKiranBuduguru-7986 ·

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

0 Votes 0 ·
Show more comments

Hi @ChaitanyaKiranBuduguru-7986, have you resolved your issue? any update for this?

0 Votes 0 ·

There is no blocking now. Thanks a lot for your help.

0 Votes 0 ·
CarrinWu-MSFT avatar image CarrinWu-MSFT ChaitanyaKiranBuduguru-7986 ·

You are welcom, have a nice day!

0 Votes 0 ·
CarrinWu-MSFT avatar image
1 Vote"
CarrinWu-MSFT answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.