Interesting Topic Today:

In general sense, we know that, internally within operating system, any work is performed using worker threads. Similarly, Sql Server relies on Windows Operating systems thread services to perform work; Each running query has a separate thread. When we have a large number of client requests, assigning a dedicated thread to each request can consume a large amount of resources. So, Sql Server and OS internally manage this efficiently with Thread Pooling.

Thread Pooling allows to bucket of threads that are used to service any client requests. If the number of requests are lower than the number of total threads, then each request gets a dedicated thread. But when the number of requests exceeds the total threads (in the thread pool), Sql Server manages that incoming request to assign the next available thread. So there would be some waiting. Since this is done at a high pace, multiple requests are serviced by a single thread, one after another.

Sometimes, when there are a large number of requests coming in at a faster pace than the workload is being completed, we’ll notice “application slowness” or “application timeouts” or certain error messages that say “could not establish connection to database” or “thread unavailable in connection pool” (see error message below). To reduce this ‘lack of threads’ issue, we could create start with a higher count of threads at OS startup.

Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  
This may have occurred because all pooled connections were in use and max pool size was reached.
Error Number:  5 on <instance name>

In Thread Pooling, the number of threads made available at start up is determined by this max worker threads option. By default, the value is set to 0 —  meaning, Sql Server will manage it internally. The maximum allowable limit is 65,535. Since, at any given point, we have limited hardware and software resources, we do not want to make this number too high. We also do not want to make it too low, as it would impact the application queries.

Configuring these threads depends on several factors — CPU being the prominent one. Each thread takes turns on the processor to complete its work – all of this is managed by SQL OS and Windows Schedulers. Microsoft has proposed some general recommendations on this to figure out a way to get to a good number.

Note: For DBA’s, the DAC  has its own scheduler, with its own threads. So, if the server becomes unresponsive, that could be used to login.