When to change SQL 2005 max worker threads on 64-bit server

Server Environment:

• Windows 2003 Standard R2 x64 SP2


• SQL 2005 Enterprise 64-bit SP2



• HP ProLiant BL460c G1, Xeon E5440 2.83 Ghz processors (Quad-core)


• 8 GB RAM

EDIT: I should also note that max_workers_count is currently at the default 512 for a 4 processor box

We're running into threadpool deadlocks that I'm fairly sure are the related to parallelism. The deadlock graphs are nearly identical to those in Bart Duncan's post about Intra-Query Parallel Thread Deadlocks, and I don't see any mention of lock resources in the deadlock output, as mentioned in the Caveats section of his post which is what leads me to believe this is a parallelism thing.

I'm in the process of tuning the queries that look to be related to these, but this will take a little while (read "a couple of weeks"). In the meantime, I am wondering if upping the threadpool would be wise or not as a temporary workaround.

Any SQL Jocks out there want to help a guy out?

(BTW - going to SP3 is not an option right now because of this problem)

Answer

Upping the number of workers won't affect your deadlock scenario at all if it is related to parallelism as in Bart Duncan's blog post. If it is truly a parallel deadlock, your quick fix is to OPTION(MAXDOP n) the offending query while you are working on tuning it, and restrict it back to the point where the deadlocking ceases. You may not need to go back to DOP 1 necessarily, I've seen DOP 4 fix it before.

Another thing to look at is if hyperthreading is enabled on the server, disabling it. The SQLOS creates a user scheduler for each logical CPU available to SQL Server. With hyperthreading, you get 8 logical CPU's which means that you have 8 user schedulers. Your query may be running at DOP 8 when you really have 4 CPU's which could be leading to your problem. You can tell if this is part of the problem by counting the number of process nodes in the deadlock XML graph. If you have 8 process nodes then you should try disabling hyperthreading on the server and see if that solves the problem.