A few days ago, I ran into several interesting issues, one of them was documented in a previous post here.  Today, I’m going to write about another issue that occurred under similar circumstances. THREADPOOL wait time. Sql Server has its own scheduling mechanism that is part of its ‘operating system’ called SQLOS. It is responsible for scheduling and synchronization of threads along with several other things.

Upon successful authentication of a login, Sql Server looks for an available thread to be assigned to this new login/request. When all the available threads are taken, Sql Server assigns this THREADPOOL wait for this new connection as it is trying to find an available thread. In simpler terms, Sql Server is hung and cannot issue a thread for new connections. At that point, it makes an entry into the Sql Server error log with following message.

New queries assigned to process on Node 0 have not been picked up by a worker thread 
in the last 60 seconds. Blocking or long-running queries can contribute to this 
condition, and may degrade client response time. Use the "max worker threads" 
configuration option to increase number of allowable threads, or optimize current 
running queries. 

As you can see, it could because of several things. If ‘max worker threads’ is reached, Sql Server cannot create any more new threads. We either need to increase that number or find out why the existing connections are not freeing up the threads. May be there is blocking that is preventing any progress. Using DAC connection would help in these situations to resolve (or KILL) the offending processes (SPIDs) and open up resources. DAC has its own scheduler, memory node, etc so, a connection is usually successful in these situations.

If you instead want to increase ‘max worker threads’, we keep to keep other factors in mind. By default, this setting is set to 0 (zero), meaning unlimited threads. Sql Server is smart enough to know when to create new threads and destroy old (and inactive) threads. So, leaving it to the deafult value (0) is not a bad idea. But if constantly run into issues I mentioed in this previous post, may be re-configuring this to a more appropriate setting is fruitful. Microsoft, says the following as the better practice.

Keep a close monitor on the production system and adjust this setting based on what works for your environment. There is no one setting that works for all.

Hope this helps,

_Sqltimes