August 8, 2013 at 6:50 am
Is there a way to set up a trace that will show why you are running out of workers?
I know that I will need to capture blocking, but is there a way to track the workers themselves?
August 8, 2013 at 6:58 am
What errors/messages suggest that you're running out of worker threads?
What version of SQL, what processor architecture, how many cores?
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
August 8, 2013 at 8:02 am
Unable to log into the server and ...
SQL Server Alert System: 'Out of Workers' occurred on <Server Name>
DATE/TIME:7/17/2013 8:53:43 AM
DESCRIPTION:New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 300 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%. System Idle: 95%.
COMMENT:(None)
August 8, 2013 at 8:05 am
IT will not allow us to use the SA account, so there is no way.. that I know of .. to get into the server when it is effected. Once you can get in.. my blocking query shows no problems, and running a listing of workers shows just the normal stuff.
August 8, 2013 at 8:22 am
GilaMonster (8/8/2013)
What version of SQL, what processor architecture, how many cores?
The DAC connection requires a sysadmin, not necessarily SA, so you should be able to use that to connect.
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
August 8, 2013 at 8:38 am
August 8, 2013 at 3:54 pm
If you run out of workers, this is likely to be due to one of the following (or a combination thereof):
1) There are simply too many active users.
2) There are too many parallel queries.
3) You have set "Max worker threads" to a low value.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 9, 2013 at 7:11 am
GilaMonster (8/8/2013)
GilaMonster (8/8/2013)
What version of SQL, what processor architecture, how many cores?The DAC connection requires a sysadmin, not necessarily SA, so you should be able to use that to connect.
When the Sql Server instance stops responding.. and I try to connect using Management Studio, it times out.
We are running 2008 R2 Standard, two CPU's with 6 cores each.
August 9, 2013 at 7:20 am
Erland Sommarskog (8/8/2013)
If you run out of workers, this is likely to be due to one of the following (or a combination thereof):1) There are simply too many active users.
2) There are too many parallel queries.
3) You have set "Max worker threads" to a low value.
Worker threads are set to zero, default.
August 9, 2013 at 7:47 am
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
August 9, 2013 at 9:56 am
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.
August 9, 2013 at 10:28 am
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
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply