Large Sleep counts causing THREADPOOL wait?

Question

When I run sp_whoisactive throughout the day, I usually see we have around 500-700 connections on our server. We are occasionally getting THREADPOOL waits and high signal wait ratios (so we are thread starved at times).

However, these 500-700 connections only 10-25 of them are active. I wasn't sure if a sleeping SPID is still bound to a thread while it sleeps?

Could that be causing our high signal wait ratio and THREADPOOL waits?

I have a feeling our 3rd party application is not properly closing out connections and is causing us issues, but wanted to check first before I bring this up with them. Thanks!

Answer 1

What I really want to know is do sleeping sessions hold a thread?

No, they do not,

A SQL Server worker thread, also known as worker or thread, is a logical representation of an operating system thread. When executing serial requests, the SQL Server Database Engine will spawn a worker to execute the active task.

But instead of actually spawning a new worker, one is typicall available in a pool

The max worker threads option enables SQL Server to create a pool of worker threads to service a larger number of query requests, which improves performance.

Thread and Task Architecture Guide -- SQL Server task scheduling

which you can see from running

select count(*)
from sys.dm_os_workers

select count(*)
from sys.dm_exec_sessions

Since you typically have more sessions than workers.

Answer 2

I suppose they could be bound to a thread while they are inactive, but they shouldn't be causing threadpool waits if they are just sitting there. I'd look at what those active sessions are doing. See if they have a "CXPACKET" wait type, that could show that those active sessions are using multiple threads to perform their actions. Here is a good article about CXPACKET with some troubleshooting tips: https://www.mssqltips.com/sqlservertip/2027/a-closer-look-at-cxpacket-wait-type-in-sql-server/