Suspended Commands on master db

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Suspended Commands on master db

Suspended Commands on master db

Rate Topic

Display Mode

Topic Options

Author

Message

ProofOfLife

Posted Sunday, September 30, 2012 11:23 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 9:11 PM
Points: 45, Visits: 9,440

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

Post #1366319

GilaMonster

Posted Monday, October 01, 2012 1:14 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 37,726, Visits: 29,985

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 2008, MVP
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

Post #1366336

ProofOfLife

Posted Monday, October 01, 2012 3:31 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 9:11 PM
Points: 45, Visits: 9,440

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

Post #1366421

GilaMonster

Posted Monday, October 01, 2012 3:38 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 37,726, Visits: 29,985

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 2008, MVP
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

Post Attachments

SystemProcesses.jpg (82 views, 171.76 KB)

Post #1366429

ProofOfLife

Posted Monday, October 01, 2012 1:46 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 9:11 PM
Points: 45, Visits: 9,440

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

Cheers

Post #1366749

harikasomireddy

Posted Friday, April 26, 2013 12:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:41 PM
Points: 1, Visits: 3

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
2875692330 ONDEMAND_TASK_QUEUE
2875659216 BROKER_TRANSMITTER
2875659216 BROKER_TRANSMITTER
2875659199 KSOURCE_WAKEUP
288366960 XE_DISPATCHER_WAIT
10602506 BROKER_EVENTHANDLER
4613707 LAZYWRITER_SLEEP
4613656 LAZYWRITER_SLEEP
4613654 LAZYWRITER_SLEEP
4613646 CHECKPOINT_QUEUE
447861 FT_IFTSHC_MUTEX
390142 FT_IFTS_SCHEDULER_IDLE_WAIT
29701 XE_TIMER_EVENT
24375 FT_IFTS_SCHEDULER_IDLE_WAIT
3086 SQLTRACE_INCREMENTAL_FLUSH_SLEEP
2313 REQUEST_FOR_DEADLOCK_SEARCH
608 LAZYWRITER_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.

Post #1447116

« Prev Topic | Next Topic »

Permissions