0
Answer

too much slow when check long wrong query i found status suspend with

I work on sql server 2019 i run my stored procedure on sql server .

it take may be 5 hours so i try to trace why it take too much time or too long time .

so I make this query to trace issue on my procedure

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 -- What SQL Statements Are Currently Running?
 SELECT [Spid] = session_Id
     , ecid
     , [Database] = DB_NAME(sp.dbid)
     , [User] = nt_username
     , [Status] = er.status
     , [Wait] = wait_type
     , [Individual Query] = SUBSTRING (qt.text, 
          er.statement_start_offset/2,
     (CASE WHEN er.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
         ELSE er.statement_end_offset END - 
                             er.statement_start_offset)/2)
     ,[Parent Query] = qt.text
     , Program = program_name
     , Hostname
     , nt_domain
     , start_time
 FROM sys.dm_exec_requests er
 INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
 CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
 WHERE session_Id > 50              -- Ignore system spids.
 AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
 ORDER BY 1, 2

individual query suspend as below

update m set m.ShelfLifeConditiontext=nr.Name from #final m
inner JOIN dbo.Nop_AcceptedValuesOption nr WITH(NOLOCK) ON nr.AcceptedValuesOptionID = m.ShelfLifeConditin

I see query update m set as above with status suspend

so what i do to solve issue of long time process for process suspend with wait log buffer please ?

Spid ecid Database   Status Wait Individual Query Program Hostname start_time
114 0 Z2DataCore suspended LOGBUFFER  update m set m.ShelfLifeConditiontext=nr.Name from #final m .Net SqlClient Data Provider                                                                                                     Z2D-DB6                                                                                                                          06:26.7