We are seeing application time outs and with help of the First Responder Kit, I have captured details. One of the things that caught my attention was:
Poison wait detected: Thread Pool
So I ran below queries:
---get thread pool waits---thread pool wait query
SELECT count(*) FROM sys.dm_os_waiting_tasks
where wait_type ='threadpool'
-- get threads available--Threads available query
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',
Getdate() as 'DateLogged'
from
sys.dm_os_Schedulers where status='VISIBLE ONLINE'
When I run the "thread pool wait" query, thread pool waits are appearing in above result (sometimes, more than 20 results) for fraction of second and then disappearing when I run it again. But I could see more than 400 threads available in second result set through the "threads available" query.
I am not able to understand why I am seeing THREADPOOL
waits when I run the first query, but second query shows threads are still available. Can any one please explain?
- Maxdop: 8
- Max degree of parallelism: 50