SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Suspended Commands on master db


Suspended Commands on master db

Author
Message
ProofOfLife
ProofOfLife
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 9474
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216603 Visits: 46278
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
ProofOfLife
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 9474
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216603 Visits: 46278
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


Attachments
SystemProcesses.jpg (869 views, 171.00 KB)
ProofOfLife
ProofOfLife
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 9474
Thanks for that Gail. I'll stop obsessing over them and get on with looking at things that are really amiss then Smile

Cheers
harikasomireddy
harikasomireddy
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 8
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search