We recently had a problem on our SQL Server 2014 HADR environment, where one of the servers ran out of worker threads. Got the message:
The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread because there are not enough available worker threads
While we were able to "solve" the problem by moving one of the Availability Groups to another server, I was wondering if it is possible to see which queries run on which scheduler (or worker, or task).
With the following query I'm able to see how many workers are available, in use and waiting for resources:
declare @max int
select @max = max_workers_count from sys.dm_os_sys_info
@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'
sys.dm_os_Schedulers where status='VISIBLE ONLINE'
And with the following query, I'm able to see which workers are running on which CPU (core):
FROM sys.dm_os_Schedulers s --> Prozessoren Kerne
JOIN sys.dm_os_workers w ON w.scheduler_address = s.scheduler_address
JOIN sys.dm_os_tasks t ON t.task_address = w.task_address
WHERE s.status = 'VISIBLE ONLINE'
AND s.cpu_id = 2
Is there some way to find which SPID (and in the end which query) is running on which thread?
I was already searching for a while and found some interesting information about the connection between schedulers, workers and threads, but nothing that really showed me if it is possible:
- Understanding SQL Server Schedulers, Workers and Tasks by Daniel Farina
- Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests – what does it all mean? by _Jo.Pi_
I would like to see which database uses so many worker threads. We have a few databases which (in my opinion) do not belong on the production server. When I check
sys.dm_exec_requests there seems not much going on.
The environment has run in the same configuration for more than 1 year without problems. The server in question has 24 CPUs and 5 AGs on it, with a total of 325 databases. 3 AGs are primary. To work around the issue we failed an AG with 50 databases from that server over to the secondary.