Suspended Commands on master db

Question

Suspended Commands on master db

ProofOfLife

Ten Centuries

Points: 1332
More actions
September 30, 2012 at 11:23 pm

#264940

Hi
I'm hoping someone will be able to cast some light on my current server issue.
There are a number of commands which on the master db which have a suspended status, with significant wait times along with them.
Command........................Wait Time...........Wait Type
SIGNAL HANDLER..............269275190..........KSOURCE_WAKEUP
TASK MANAGER................269282488..........ONDEMAND_TASK_QUEUE
BRKR EVENT HNDLR...........269276124..........BROKER_EVENTHANDLER
BRKR TASK......................269282100..........BROKER_TRANSMITTER
BRKR TASK......................269282095..........BROKER_TRANSMITTER
FT GATHERER...................470649..............FT_IFTS_SCHEDULER_IDLE_WAIT
(Apologies for my crude table)
None of these have a Blocked By SPID listed against them. If my calculations are correct, the longer suspended tasks were all put into this state around 72 hours ago - in my part of the world that means Friday evening at around 6pm. This points the finger at one or a combination of scheduled jobs involved in processing XML data rerieved from an external webservice. Unfortunately the logs that far back have now been purged - cause for me to reconsider our clean up regime.
From my research so far I get it that these are system tasks and as such they cannot be killed. I also understand that the BRKR tasks are created by the Service Broker and probably relate to our use of DB Mail or some other background operation.
I'm seeking to understand what may have given rise to these and why. Later this evening I will restart the server, expecting these tasks to clear during that process. The need to uderstand what is going on is driven by wanting to avoid them in the future as I suspect that they have played a role in very poor performance from the server today.
I look forward to someone perhaps being in a position to provide some possible explanation of this situation. My thanks in advance.
Cheers
Rowan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Those are all system processes and they are all waiting for something to wake them up. This is correct and expected behaviour, the system processes don't run all the time.

Don't restart the server, there's nothing wrong here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ProofOfLife Ten Centuries Points: 1332 More actions · Answer 2

Hi Gail.

Thanks for the response.

Is it normal for them to have to wait so long - I can't imagine any of our processes which might have that sort of delay. The jobs which we use for handling the comms with the webservice and all the data movement are happening at much shorter intervals - some at 10 minute, others at 15, a couple at 60 min and then the rest four hourly.

Is there any way to determine what processes actually created these tasks. From my reading to date it does not appear possible to do so.

Cheers

Rowan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Yes, absolutely it's normal.

No user process created these. They were spawned when SQL started and they won't terminate until the SQL instance stops. That's why they have such huge wait times. System processes all wait a lot, it's normal, that's why those wait types are excluded from all well-written wait-analysis scripts. They're waiting for any work that they need to do.

This is what my desktop PC's SQL instance looks like, machine's been up for a week and SQL's barely been used in that time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ProofOfLife Ten Centuries Points: 1332 More actions · Answer 4

Thanks for that Gail. I'll stop obsessing over them and get on with looking at things that are really amiss then 🙂

Cheers

harikasomireddy SSC Journeyman Points: 75 More actions · Answer 5

Hi All,

its just same like above mentioned issue but i am asking to get more clarity on this

anyone Could you please let me know whether the following statistics will impact the system performance or not.

The following are the wait times for respective wait types. This is the report i got from a dev server. As per my conversation with the DEV team 'they ran some script on the server so the wait times got increased like this'.

Now my question is if i ran the same on the production server will it effect the perfomance.

waittime lastwaittype

2875692330ONDEMAND_TASK_QUEUE

2875659216BROKER_TRANSMITTER

2875659199KSOURCE_WAKEUP

288366960XE_DISPATCHER_WAIT

10602506BROKER_EVENTHANDLER

4613707LAZYWRITER_SLEEP

4613656LAZYWRITER_SLEEP

4613654LAZYWRITER_SLEEP

4613646CHECKPOINT_QUEUE

447861FT_IFTSHC_MUTEX

390142FT_IFTS_SCHEDULER_IDLE_WAIT

29701XE_TIMER_EVENT

24375FT_IFTS_SCHEDULER_IDLE_WAIT

3086SQLTRACE_INCREMENTAL_FLUSH_SLEEP

2313REQUEST_FOR_DEADLOCK_SEARCH

608LAZYWRITER_SLEEP

I am new to this forum If any descrepency in my mail excuse me. If any details i have to mention please let me know.