When threads run amuck.

Sometimes more isn't always better. I ran into a situation where the number of threads being spawned for a particular process was clogging up the server. The machine this query was running on had 16-cpus running sql server 2005 with 16GB of memory, far from a slouch. I fired up this query and did a quick check of the active processes to find over 40 threads attached to these processes. To add to this I had 8 of these queries going. I figured this would be great, parallel processing, I should be done in no time at all. Well...not quite. I ran into an error at night:

Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

I couldn't find anything anywhere on this.  Spoke with Microsoft and the short answer is I should tone down my threads.  It seems as though there is alot of contention for resources (disk being one of them) and this caused a bottleneck.  Rather than adjust the serverwide max parallelism, I took the more conservative approach and corrected my process using OPTION (MAXDOP N).

MAXDOP tells sql server to use N processors in determining the number of threads to create.  Its kind of tricky since you cannot tell sql server "use X threads", what you do is tell sql server "I want you to only use 8 CPUS in your calculation for the number of threads to use". 

In my case I decided to use a maxdop of 8 and that solved the problem.