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 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'
And with the following query, I’m able to see which workers are running on which CPU (core):
SELECT * 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.
Thanks to Solomon Rutzky:
Have you tried correlating to the
I was able to get the information I was looking for.
With this query, I can see which session is using which CPU_ID (scheduler):
SELECT s.cpu_id, s.status, db_name(r.database_id) as [databaseName], w.last_wait_type, w.return_code, t.task_state, t.pending_io_count, t.session_id, r.sql_handle FROM sys.dm_os_Schedulers s 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 JOIN sys.dm_exec_requests r ON r.scheduler_id = s.scheduler_id order by 1,3
To get the SQL statements that are running, I change the query to:
SELECT s.cpu_id, s.status, db_name(r.database_id) as [databaseName], w.last_wait_type, w.return_code, t.task_state, t.pending_io_count, t.session_id, r.sql_handle, te.text FROM sys.dm_os_Schedulers s 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 JOIN sys.dm_exec_requests r ON r.scheduler_id = s.scheduler_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) te order by 1,3
But that only gives me (obviously) the tasks that have an SQL_handle.
It seems that most of the threads on that server are being used by the system itself to keep everything in sync. Most of the workers are being used as “system jobs”. The
last_wait_type of those tasks is mostly
Although I have the answer I was looking for, I still didn’t find the source of the problem. I’m going to open another question for that ( Who is using my worker threads? SQL Server 2014 – HADR ).