Sql-server – Large Sleep counts causing THREADPOOL wait

performancesql serverwait-types

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!

Best Answer

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.

Related Question