January 31, 2013 at 10:25 am
This is on SQL Server 2005 SP4.
We have a long stored procedure where one query will, at times, cause the process to go into a suspended state with a wait type of SLEEP_TASK. This will last several hours and basically requires that we kill the process because it will eventually end up blocking other processes that are utilizing this same stored procedure.
My research on SLEEP_TASK waits had me concentrating on watching for hash match spill overs to tempdb and parallelism based on several articles and posts that I have read. I went ahead and monitored for this and while this does occur for some queries in the stored procedure, the one that ends up being suspended with the SLEEP_TASK wait doesn't have any hash matches or isn't using parallelism. We do have some sort warnings when this query runs, but I haven't noticed those being associated with the SLEEP_TASK wait. In any event, I also monitored for tempdb contention and there doesn't seem to be any issue there either.
My question is this. What other things can one look for when dealing with suspended processes with a SLEEP_TASK wait that never finish?
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply