Sql-server – Is it possible to see which SPID uses which scheduler (worker thread)

availability-groupsdmvsql serversql server 2014

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:

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.

Best Answer

Thanks to Solomon Rutzky:

Have you tried correlating to the scheduler_id column in sys.dm_exec_requests?

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 REDO_THREAD_PENDING_WORK or HADR_WORK_QUEUE.

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

Related Question