why status suspend fo this query and how to solve it ?

Question

I work on sql server 2019 i run stored procedure on 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 suspend

so what ido to solve issue please ?

i attached parent query with post

Answer 1

Did you try sp_who2 and see if SPID 114 is blocked (BlkBy column in the output) by another process? If yes, you may need to kill the blocking process so that the process SPID 114 is able to go forward.

Answer 2

The server can only do so many tasks at the same time. It is perfectly normal for tasks to be "suspended" for short periods of time. This is not a problem or an indication of a problem. Simply running the query to get suspended tasks, may cause other queries to be suspended while it runs the query.

However, the wait state on this particular query is a log related wait state which generally indicates a low memory issue.

See:

https://www.mssqltips.com/sqlservertip/4131/troubleshooting-sql-server-transaction-log-related-wait-types/#:~:text=According%20to%20Microsoft%2C%20the%20LOGBUFFER,a%20log%20flush%20to%20complete.

Answer 3

I think LOGBUFFER also can suggest that log writes are taking a wee bit too long time, so you may need to review the hardware.

It may also help to add this condition to the qurey:

WHERE NOT EXISTS (SELECT m.ShelfLifeConditiontext INTERSECT SELECT nr.Name)

Then you will not try to update rows that already has the desired value.

Answer 4

Hi @ahmed salah ,

Have you tried to run the same query as the stored procedure? What's the difference between them in terms of time.
If the time of running query directly, it may be the parameter sniffing issue.
Please see this and use the option WITH RECOMPILE: https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.