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.
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.
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