10

We had recently a problem on our SQL Server 2014 HADR environment, where one of the servers ran out of worker threads.

We got the message:

The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread because there are not enough avaiable worker threads.

Error message when it started

I already opened another question, to get a statement which (I thought) should help me analyze the problem (Is it possible to see which SPID uses which scheduler (worker thread)?). Although I have now the query to find the threads which are using the system, I don't understand why that server ran out of worker threads.

Our environment is as follows:

  • 4 Windows Server 2012 R2
  • SQL Server 2014 Enterprise
  • 24 Processors --> 832 Worker threads
  • 256 GB Ram
  • 12 Availability Groups (overall)
  • 642 Databases (overall)

So, the server which had the problem had the following configuration:

  • 5 Availability Groups (3 Primary / 2 Secondary)
  • 325 Databases (127 Primary / 198 Secondary)
  • MAXDOP = 8
  • Cost Threshold for Parallelism = 50
  • Power plan is set to "High performance"

To "resolve" the problem we manually failed one Availability Group over to the secondary server. The configuration of that server is now:

  • 5 Availability Groups (2 Primary / 3 Secondary)
  • 325 Databases (77 Primary / 248 Secondary)

I'm monitoring the available threads with this statement:

declare @max int
select @max = max_workers_count from sys.dm_os_sys_info

select 
    @max as 'TotalThreads',
    sum(active_Workers_count) as 'CurrentThreads',
    @max - sum(active_Workers_count) as 'AvailableThreads',
    sum(runnable_tasks_count) as 'WorkersWaitingForCpu',
    sum(work_queue_count) as 'RequestWaitingForThreads' ,
    sum(current_workers_count) as 'AssociatedWorkers'
from  
    sys.dm_os_Schedulers where status='VISIBLE ONLINE'

Normally the server has around 250 - 430 worker threads available, but when the issue started there were no workers left.

-119 available threads

Today, out of nowhere, the available workers dropped from 327 down to 50, but only for a minute and went then back up to around 400.

I already saw the other question (HADR high worker thread usage) but it does not help me.

Our system ran stable for over a year without any problems. We haven't had any failover or other major change in the distribution of the databases.

We are using "Synchronous commit" between the replicas. From my understanding there is no compression involved, see Tune compression for availability group in the documentation.

Does any one have an idea what is using all the worker threads?

EDIT: Found this page where there is a lot of information about exactly those issues http://www.techdevops.com/Article.aspx?CID=24

1

Community wiki answer:

You have a high number of databases in availability groups, that's going to be where your threads are going. There is a lot involved in the compression, encryption, and transport cost. Try turning off compression, it will reduce your thread usage by a about a third (depending on the replica count).

The question is tagged SQL Server 2014, which will by default use compression. SQL Server 2016, by default, will not use compression for sync.

You may need to increase the worker threads on the instance, or better: balance out the most active ones and inactive ones on multiple servers. See the related Q & A AlwaysON Availability group query very slow.

You may also find it is an application that is unable to close requests properly. This can result in lot of sleeping sessions lying around (which consume workers).

The number of threads actually used depends on how active the databases are. You could have 1,000 databases and, if most are idle 95% of the time, you won't have any issues. It seems that your databases have become active more often and have eaten more of your threads. That's the long and short of it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.