Stored Procedure stuck at waitfor delay '00:00:02'

Question

I have a stored procedure that's supposed to run every night. The procedure deletes data in batches of 10k rows, and between each batch issues a WAITFOR DELAY '00:00:02' with the intention of letting other processes do things in between batches (I didn't write this, this is the intention I was given).

The job ran and has been running for a while now. I ran sp_WhoIsActive and it shows this sp is suspened with the waitfor statement the sql text. Why would sql server keep this from processing further?

Additional information:

It's been running for over a day. It should have deleted 400k records, and has deleted 10k of them.

sp_whoIsActive output:

dd hh:mm:ss.mss 01 06:32:28.126

session_id 58

sql_text

login_name [redacted]

wait_info (131ms)WAITFOR

CPU 42,655,358

tempdb_allocations 7,981,282

tempdb_current 130

blocking_session_id NULL

reads 1,862,017,930

writes 26,792,391

physical_reads 273,169

locks

used_memory 4

status suspended

open_tran_count 0

percent_complete NULL

database_name [redacted]

program_name SQLAgent - TSQL JobStep (Job 0xA887960BA5034D4F89EEBDA2CB934921 : Step 2)

Answer 1

This could be expected behaviour especially if you're deleting based on an index (aka where [primarykeyfield] < targetID)

since that delete will take in the ms of time you'll almost always catch the process in the wait time between the two batches

Say for example the delete takes 10ms (i imagine it will potentially take less than this but its just an example), the wait time is 2000ms

So for every time you run the usp_whoisactive command there is a 10/2010 chance of hitting that in the period of time the delete is running (less than 0.5%) and a 2000/2010 chance of catching it during the wait for delay.

If you want to observe it directly find the spid that its running over and run the profiler over it checking for the SP:StmtCompleted search and you'll see the delete occurring every 2s and going into the waitfor (note if you wan to see the start of the waitfor as well check the SP:StmtStarting but the completed one will give you the duration of how long it takes to run each part.

Answer 2

@Ste Bov was right in the sense that I kept hitting the waitfor delay when running sp_WhoIsActive. The real issue was that in my delete loop it was throwing an error because it couldn't delete due to a referential integrity constraint. It was trying to delete the same 10k rows over and over again and never making progress, but still saw that it had records to delete so it kept going.

So SQL Server wasn't stuck on the waitfor delay task, it just looked like it.