Broker task stop

[simon]

I'm analyzing server performance counters and also sql server
activity: locks, and so on...

The only problem I have is with locks.
What I can see for example is:
BROKER_TASK_STOP 3510 completed waits wait time
1.145 ms/sec %of total wait time: 63,48%
and
SOS_SCHEDULER_YIELD 1552974 completed waits wait time
0,203 %of total wait time: 25,49%

The first one is from sql service broker. But I disabled it on my
database:
ALTER DATABASE myDatabase SET DISABLE_BROKER

But it is still enabled in msdb and tempdb. Anybody know, why?(because
I didn't explicitly enabled it there, maybe some other person did, but
I guess it is automaticly enabled)
Should i disabled it on temp and msdb and enabled it just on my
database(because I would like to use it)?

And what about the SOS_SCHEDULER_YIELD?

I can't found no other problem. No long running queries, no network
problems, no disk problems, average CPU is about 20%.
When I have problems(couple of minutes every day) always I can see
similar picture as I described. Everything is normal,
only locks are higher as I described.

Any idea?

br, Simon

[simon]

Post by simon
I'm analyzing server performance counters and also sql server
activity: locks, and so on...
The only problem I have is with locks.
BROKER_TASK_STOP        3510 completed waits            wait time
1.145 ms/sec     %of total wait time: 63,48%
and
SOS_SCHEDULER_YIELD    1552974 completed waits      wait time
0,203          %of total wait time: 25,49%
The first one is from sql service broker. But I disabled it on my
ALTER DATABASE myDatabase SET DISABLE_BROKER
But it is still enabled in msdb and tempdb. Anybody know, why?(because
I didn't explicitly enabled it there, maybe some other person did, but
I guess it is automaticly enabled)
Should i disabled it on temp and msdb and enabled it just on my
database(because I would like to use it)?
And what about the SOS_SCHEDULER_YIELD?
I can't found no other problem. No long running queries, no network
problems, no disk problems, average CPU is about 20%.
When I have problems(couple of minutes every day) always I can see
similar picture as I described. Everything is normal,
only locks are higher as I described.
Any idea?
br, Simon

I have also some other question, which I don't understand.

If I run query:

SELECT * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC

I get the most time consuming query which has execution count=1 and
total_worker_time=669116209 and last execution time 2012-01-24
02:00:00.
The query is actually executed by sql server agent job, once a day at
2:00 in the morning and it last about 20 minutes.

But if total worker time is 669116209, it means it takes about 185
hours? How is that possible?
Even if I divide this by 4 CPU cores, it is still 46 hours.

I have also some other queries with similar excecution stats, because
at night I calculates some data for reports.

Any idea?

br, Simon

[Erland Sommarskog]

Post by simon
I have also some other question, which I don't understand.
SELECT * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC
I get the most time consuming query which has execution count=1 and
total_worker_time=669116209 and last execution time 2012-01-24
02:00:00.
The query is actually executed by sql server agent job, once a day at
2:00 in the morning and it last about 20 minutes.
But if total worker time is 669116209, it means it takes about 185
hours? How is that possible?

No, that's 11 minutes. The value is in microseconds, not milliseconds.

[Erland Sommarskog]

Post by simon
I'm analyzing server performance counters and also sql server
activity: locks, and so on...
The only problem I have is with locks.
BROKER_TASK_STOP 3510 completed waits wait time
1.145 ms/sec %of total wait time: 63,48%
and
SOS_SCHEDULER_YIELD 1552974 completed waits wait time
0,203 %of total wait time: 25,49%
The first one is from sql service broker. But I disabled it on my
ALTER DATABASE myDatabase SET DISABLE_BROKER

Not all wait types are cause for alarm. After all, what can an idle server
do but wait?

There are a number of wait types you should ignore. I don't have a reference
to a list of ignorable types right now, but I'm fairly sure that this is a
type you can ignore. I tried a development server we have, BROKER_TASK_STOP
is #5 on the list. On the instance on my own workstation (which I use
rarely), BROKER_TASK_STOP is #10 with 2 waits with a total of 10 seconds.

Books Online says: "Occurs when the Service Broker queue task handler tries
to shut down the task. The state check is serialized and must be in a
running state beforehand."

So my gut feeling is that this is no cause for concern.

Post by simon
And what about the SOS_SCHEDULER_YIELD?

SOS_SCHEDULER_YIELD occurs when a process yields voluntary to permit another
process to execute. This means that at some point you have more tasks
running that there are available CPUs. Not too uncommon in a multi-user
system.

If these are your two top waits, I would not be too alarmed. It's more
common to see CX_PACKET, ASYNC_NETWORK_IO and LCK_xxxx at the top. In
which case, the system can benefit from some tuning. (Well, in the case
of ASYNC_NETWORK_IO, it's the client that needs fixing.)

[simon]

Post by Erland Sommarskog

Post by simon
I'm analyzing server performance counters and also sql server
activity: locks, and so on...
The only problem I have is with locks.
BROKER_TASK_STOP        3510 completed waits            wait time
1.145 ms/sec     %of total wait time: 63,48%
and
SOS_SCHEDULER_YIELD    1552974 completed waits      wait time
0,203          %of total wait time: 25,49%
The first one is from sql service broker. But I disabled it on my
ALTER DATABASE myDatabase SET DISABLE_BROKER

Not all wait types are cause for alarm. After all, what can an idle server
do but wait?
There are a number of wait types you should ignore. I don't have a reference
to a list of ignorable types right now, but I'm fairly sure that this is a
type you can ignore. I tried a development server we have, BROKER_TASK_STOP
is #5 on the list. On the instance on my own workstation (which I use
rarely), BROKER_TASK_STOP is #10 with 2 waits with a total of 10 seconds.
Books Online says: "Occurs when the Service Broker queue task handler tries
to shut down the task. The state check is serialized and must be in a
running state beforehand."
So my gut feeling is that this is no cause for concern.

Post by simon
And what about the SOS_SCHEDULER_YIELD?

SOS_SCHEDULER_YIELD occurs when a process yields voluntary to permit another
process to execute. This means that at some point you have more tasks
running that there are available CPUs. Not too uncommon in a multi-user
system.
If these are your two top waits, I would not be too alarmed. It's more
common to see CX_PACKET, ASYNC_NETWORK_IO and LCK_xxxx at the top. In
which case, the system can benefit from some tuning. (Well, in the case
of ASYNC_NETWORK_IO, it's the client that needs fixing.)
--
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you for your answer.

"Occurs when the Service Broker queue task handler tries to shut down
the task"

I don't understand what task if I had disabled service broker queue on
my database.
It's still enabled on temp and msdb database but I don't send any
messages from anywhere to any queue.

What is interesting is that users have problems from 1 to 10 minutes
2-3 times a day.
The pages execution time increase from 1 second to couple of minutes.

I have performance counters and also I trace server activity and query
statistics from DMW.
And every time users have problems the only increased parameter is
locks.
Everything else is more or less constant over day (CPU, network, I/
O,...)
And locks are always mostly from BROKER_TASK_STOP so, I guess it is
connected some how.

I will include also quest software, which has more detailed analysis
on user friendly way.

Thank you for your help,
Simon

[Erland Sommarskog]

Post by simon
"Occurs when the Service Broker queue task handler tries to shut down
the task"
I don't understand what task if I had disabled service broker queue on
my database.

Nor do I, but my hunch is that is nothing to bother about.

Post by simon
What is interesting is that users have problems from 1 to 10 minutes
2-3 times a day.
The pages execution time increase from 1 second to couple of minutes.
I have performance counters and also I trace server activity and query
statistics from DMW.
And every time users have problems the only increased parameter is
locks.
Everything else is more or less constant over day (CPU, network, I/
O,...)
And locks are always mostly from BROKER_TASK_STOP so, I guess it is
connected some how.

So this BROKER_TASK_STOP would go and lock things in the user database? That
seems unlikely to me.

There is more than one tool to analyse locks, but my favourite (because I
wrote it myself :-) is beta_lockinfo, which you can get from
http://www.sommarskog.se/sqlutil/beta_lockinfo.html
When you have a blocking situation, beta_lockinfo can quick tell you who is
blocking whom and what resource are waiting for.