Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Running out of workers Expand / Collapse
Author
Message
Posted Friday, August 9, 2013 7:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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.
Post #1482763
Posted Friday, August 9, 2013 7:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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.
Post #1482775
Posted Friday, August 9, 2013 7:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 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 #1482779
Posted Friday, August 9, 2013 9:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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.
Post #1482846
Posted Friday, August 9, 2013 10:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 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 #1482855
Posted Friday, August 9, 2013 1:41 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:24 PM
Points: 805, Visits: 722
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
Post #1482920
Posted Monday, August 12, 2013 1:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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.
Post #1483455
Posted Thursday, October 3, 2013 3:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:15 AM
Points: 36, 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.
Post #1501361
Posted Friday, October 4, 2013 2:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1501767
Posted Friday, October 11, 2013 7:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:15 AM
Points: 36, 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 :) ]
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.
Post #1503993
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse