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


Running out of workers


Running out of workers

Author
Message
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 Visits: 775
John Mitchell-245523 (8/9/2013)
Did you follow the link I posted to watch the video? You need to use DAC.

John


Not yet.. still reviewing the posts.
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 Visits: 775
John Mitchell-245523 (8/9/2013)
Did you follow the link I posted to watch the video? You need to use DAC.

John


I am getting an error that DAC is not supported. I will put in a request to IT, but they have locked the crap out of our new servers.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86150 Visits: 45229
dwilliscp (8/9/2013)
We are running 2008 R2 Standard, two CPU's with 6 cores each.


32 bit or 64 bit?

Have you enabled remote DAC? If not, you'll have to connect from the server directly.

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


dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 Visits: 775
GilaMonster (8/9/2013)
dwilliscp (8/9/2013)
We are running 2008 R2 Standard, two CPU's with 6 cores each.


32 bit or 64 bit?

Have you enabled remote DAC? If not, you'll have to connect from the server directly.



Sorry should have added.. 64bit. Any change that would effect Disaster Recover, has to be done by the App Admin team. So I put in a request, and will see if they make the change. I know when I asked to get worker threads set to 1,000 from the 800 or so that it currently is.. they wanted me to prove that the problems we are having was worker threads. Thus this posting.. on trying to track their use and what happens when they run out.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86150 Visits: 45229
I wouldn't recommend changing worker threads. 0 is a good default, it shouldn't need changing. Identifying why you're running out of worker threads should be a priority.

Maybe schedule a job to run every couple minutes that runs various diagnostic scripts and inserts results into a monitoring database.

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


Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2093 Visits: 872
dwilliscp (8/9/2013)
[quote]Sorry should have added.. 64bit. Any change that would effect Disaster Recover, has to be done by the App Admin team. So I put in a request, and will see if they make the change. I know when I asked to get worker threads set to 1,000 from the 800 or so that it currently is.. they wanted me to prove that the problems we are having was worker threads. Thus this posting.. on trying to track their use and what happens when they run out.


So you are the DBA, but you don't have permission to connect to the server machine directly? Well, then you have more than one problem!

In the meantime, the persons who have access to the servers, will have to run the diagnostic queries for you.

In this particular case, you should be able to diagnose the issue if the DAC is enabled for remote access. If SQL Server has run out of worker threads, there are of course no threads left for new connections. But there are other problems where you may find that you cannot connect, not over a remote DAC - but where any local connection works. (These problems are known as network problems, but the DBA will get the blame nevertheless.)

/Erland

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 Visits: 775
Erland Sommarskog (8/9/2013)
dwilliscp (8/9/2013)
[quote]Sorry should have added.. 64bit. Any change that would effect Disaster Recover, has to be done by the App Admin team. So I put in a request, and will see if they make the change. I know when I asked to get worker threads set to 1,000 from the 800 or so that it currently is.. they wanted me to prove that the problems we are having was worker threads. Thus this posting.. on trying to track their use and what happens when they run out.


So you are the DBA, but you don't have permission to connect to the server machine directly? Well, then you have more than one problem!

In the meantime, the persons who have access to the servers, will have to run the diagnostic queries for you.

In this particular case, you should be able to diagnose the issue if the DAC is enabled for remote access. If SQL Server has run out of worker threads, there are of course no threads left for new connections. But there are other problems where you may find that you cannot connect, not over a remote DAC - but where any local connection works. (These problems are known as network problems, but the DBA will get the blame nevertheless.)

/Erland


Yea welcome to my world... the folks that have access are the App Admin team.. not a DBA in the group. I have tried to Remote Desktop to the server and connect..using Management Studio, but it times out. (using my login) It will not let me use Admin:<my login>, states that DAC is not supported. I was local admin ... on our 2005 servers, but when we got the new servers.. got no access. Can not even access the drives except for my user folder.
ELLEN-610393
ELLEN-610393
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 102
Gail,
I see that you said that the default 0 for worker threads should be good. What is the impact of having a process spin up several thousand worker threads? Today I was looking at the Activity Monitor and saw that there were two different processes that each had about 3,000 worker threads. This is a new system for me and I was thinking that might be bad.

We are using SQL Server 2008 R2 SP1, Enterprise 64-bit, 4 processors , 146 GB RAM

All opinions are welcomed.
Thank you.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84502 Visits: 41063
ELLEN-610393 (10/3/2013)
Gail,
I see that you said that the default 0 for worker threads should be good. What is the impact of having a process spin up several thousand worker threads? Today I was looking at the Activity Monitor and saw that there were two different processes that each had about 3,000 worker threads. This is a new system for me and I was thinking that might be bad.

We are using SQL Server 2008 R2 SP1, Enterprise 64-bit, 4 processors , 146 GB RAM

All opinions are welcomed.
Thank you.


That, my friend, sounds like a "connection leak" somewhere in the code for those two processes or the code that controls those two processes and both will certainly need to be repaired.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ELLEN-610393
ELLEN-610393
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 102
Jeff Moden (10/4/2013)
ELLEN-610393 (10/3/2013)
Gail,
I see that you said that the default 0 for worker threads should be good. What is the impact of having a process spin up several thousand worker threads? Today I was looking at the Activity Monitor and saw that there were two different processes that each had about 3,000 worker threads. This is a new system for me and I was thinking that might be bad.

We are using SQL Server 2008 R2 SP1, Enterprise 64-bit, 4 processors , 146 GB RAM

All opinions are welcomed.
Thank you.


That, my friend, sounds like a "connection leak" somewhere in the code for those two processes or the code that controls those two processes and both will certainly need to be repaired.


Jeff [or any other wise and knowledgable person Smile ]
Can you point me in a direction that will help me troubleshoot what is going on? and maybe an article that will give me more in depth knowledge of worker threads? Since I am rather new here I would like to approach the people that have created these stored procedures for BO reports with some more knowledge and details.
Thank you in advance for any assistance.
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