2

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)

  • That may be just the statement that it got caught running. How long does the delete operation take? If it's relatively quick then that's all you might see. I would get counts of the deletes remaining and run it twice (give a good amount of time in between) to see if it's still working or not. Or you can check to see if the reads/writes numbers keep going up. – Jonathan Fite Apr 11 '18 at 14:41
  • 1
    The wait delay is also two minutes (could be timing based on when you looked?), can you post the output of the sp_WhoIsActive? – Shaulinator Apr 11 '18 at 14:42
  • 1
    Can you please share the code you are running? It can be somewhat simplified but could make a big difference. – Kenneth Fisher Apr 11 '18 at 15:20
  • 1
    The fact that you have all of this read and write data seems to suggest that the procedure is working properly. It seems quite likely that the delete happens quite fast and you are just observing the wait for because it just happens to be what's going on when you look at the proc. Do those numbers keep going up if you keep checking sp_whoisactive? – Zane Apr 11 '18 at 15:40
  • 2
    Show us the code then! – ypercubeᵀᴹ Apr 11 '18 at 19:06
3

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.

  • it's deleted 10k / 400k rows in over a day. i don't think this is the issue. – DForck42 Apr 11 '18 at 15:07
1

@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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.