Click here to Skip to main content
15,935,728 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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


 -- 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, 
     (CASE WHEN er.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
         ELSE er.statement_end_offset END - 
     ,[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

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 ?
result of query above running as below
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

What I have tried:

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
Updated 2-Mar-22 21:13pm

1 solution

Well, its easy now, since you have got the query that is creating the problem. You try this query on SQL Command Window with Display Estimated Execution Plan and Include Actual Execution Plan turn on. Once you have the execution plan, you need to analyze the cost associated with each operation. You also need to check the underlying data volume taking part in the query.

It seems that you are updating large number of rows that is causing this issue.

Best of Luck
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900