0

We have 8 processes that each process a separate set of data from a table via cursor, so that multiple CPUs can be used to iterate through the data. This is used for data migration so at this point the server machine isn't being used for anything else.

On occasion, this process locks up with SQL dropping back to effectively 0 CPU usage. Every one of these processes is stuck in a WAITFOR state, going up to 30sec and back down to 0 again. We have not WAITFOR command in our SQL or on the code side, and SQL won't tell me what it's waiting for in the Activity Monitor or the DMVs. It seems to happen more on machines with lower-specced resources, but I feel SQL should eventually stop waiting for CPU once the CPU usage drops back.

Am I missing something here, is there any way we can unblock these processes?

enter image description here

2
  • Have definitely looked for locks @nbk, these processes are the only thing running on the database at the time for the migration. There are no locks anywhere at the time
    – Thomas
    Commented May 11 at 11:03
  • @JamesLupolt no, Service Broker appears to be enabled but is not being used
    – Thomas
    Commented May 12 at 22:04

2 Answers 2

2

WAITFOR can be a little tricky sometimes. You aren't really "blocked" here as you may fear. These are harmless (usually - not so much if someone is putting something like a WAITFOR in the middle of a transaction and you are actually waiting in the middle and then seeing that Session ID cause blocking.)

At the end of the day, to your initial question/thoughts - something is definitely issuing that command. SQL Server never just waits for that on it's own - so code somewhere is intentionally doing this.

Activity monitor itself can sometimes be a bit piggish in resource consumption as well and doesn't always give great details easily about what is happening.

I suggest you look to use sp_whoisactive and consider adding that to your SQL Server and see if you can see more details about who is calling this. Sometimes you'll see DBMail waiting on it, sometimes you'll see Service Broker waiting on it. But this is usually a harmless wait if no users are complaining, so in this case - I would treat the symptoms instead of the monitor and say "if no one is complaining and this isn't causing blocking, it's safe to focus on other problems."

1
  • Hi Mike, thanks for your answer. We are definitely blocked with this process as it’s the only thing running on the database for a migration and once it gets into this state it sticks in WAITFOR forever with the count of milliseconds it’s been stuck in WAITFOR resetting to 0 every 30sec. Rows from the cursor stop migrating and as they are the only thing running on the server it all stops. I will try sp_whoisactive next time I’m remoted into the customer server to see if it provides any details the DMVs and other procedures I found online don’t.
    – Thomas
    Commented May 11 at 11:04
0

This turned out to be some code deeper within a Stored Procedure that used a semaphore to check if a task had been completed, and the task was unexpectedly failing.

When the task failed, without raising an error back to the main application, the SQL for all 8 threads got stuck in an endless WAITFOR loop - so the people who said there was a WAITFOR somewhere were right...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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