getting 97% SOS_WORK_DISPATCHER and cannot find any information

Question

Post reply

getting 97% SOS_WORK_DISPATCHER and cannot find any information

sambartick 90616

SSC Eights!

Points: 947
More actions
March 7, 2018 at 8:46 am

#336445

hello,
we are investigating performance issues on our application database and since turning on Azure Automatic Tuning am getting 97% waits on
SOS_WORK_DISPATCHER
there is a single article in Google when searched for this term....can anyone help?
thanks
samuel

Viewing 13 posts - 1 through 12 (of 12 total)

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

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 1

it may not be linked to the automatic tuning....i highly doubt it is....

Sue_H SSC Guru Points: 90860 More actions · Answer 2

sambartick 90616 - Wednesday, March 7, 2018 8:49 AM
it may not be linked to the automatic tuning....i highly doubt it is....

Someone just asked about this same wait type on twitter, #sqlhelp -
Twitter #sqlhelp
Interesting that they are on Azure and see this. May be nothing but a coincidence. But have you checked workers and schedulers?

Sue

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 3

ah schedulers....yes...i have found the following:

looks like i may have been hitting max workers - one vCPU gives one scheduler
didnt think we could be hitting max workers....never been there before!!

i will investigate further....thanks for the pointer

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 4

strange...our CPU usage is under 10% and we have 90% DTU available....waiting for Azure admin to get me more information...can only see database level 🙁

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 5

there is only 1 active SPID....me

wondering what the 100 current tasks are??

also found the database levels...mentions worker threads but all look like they use a single scheduler:

we have S3 so should have 200 worker threads available but seems we are at the S2 level still

also showing 9 application sessions and 5 for me....hmmmm

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 6

oops....first query above was for my SPID....without i see about 35 application sessions

slight edit gives this:

Sue_H SSC Guru Points: 90860 More actions · Answer 7

sambartick 90616 - Thursday, March 8, 2018 2:05 AM
ah schedulers....yes...i have found the following:
looks like i may have been hitting max workers - one vCPU gives one scheduler
didnt think we could be hitting max workers....never been there before!!
i will investigate further....thanks for the pointer

Those numbers in this one aren't bad - workers are available, runnable tasks is 0, nothing in the work queue.
Did you ever get whatever info you were looking for from the Azure admin?

Sue

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 8

no...i asked one of their support engineers what the wait type was and the reply was: are you seeing any performance issues?

so im guessing they want to investigate before saying too much...i will monitor

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 9

sambartick 90616 - Wednesday, March 7, 2018 8:46 AM
hello,
we are investigating performance issues on our application database and since turning on Azure Automatic Tuning am getting 97% waits on
SOS_WORK_DISPATCHER
there is a single article in Google when searched for this term....can anyone help?
thanks
samuel

So what happens when you turn it back off? :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 10

since checking the stats it seems this wait type has been around for a while...sorry...misinformation!

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 11

sambartick 90616 - Friday, March 16, 2018 3:13 AM
since checking the stats it seems this wait type has been around for a while...sorry...misinformation!

It happens. Still and according to your original post and as unlikely as it may be, did the performance problems seem to start when you turned on the automatic tuning stuff and have you tried to see what happens if you turn it back off?

Also, if you are having performance issues, take a look at the 2nd link under "Helpful Links" in my signature line below. With a bit of information provided by you good folks, we can help on the performance issue(s) that you're suffering.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vijred Say Hey Kid Points: 681 More actions · Answer 12

I believe you are using <sys.dm_os_wait_stats> table. I am not sure if it make sense to use Server level wait stats in Azure SQL Database. I recommend to use <sys.dm_db_wait_stats>!

-Vijred (http://vijredblog.wordpress.com)