1

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)

2 Answers 2

4

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.

6
  • Great, thanks Jonathan! I have 44 process nodes in the process list of the graph from the latest deadlock (each has a different process id). The values for schedulerid are only 1-4 among all 44 of the process nodes, though, so am I right in thinking that only 4 get created? I didn't have profiler running to capture the deadlock, I'm reading it out of the log output of trace flag 1222.
    – squillman
    Jun 30, 2009 at 4:45
  • Also, do you know why the discrepancy between the default value of 512 for max_workers_count and BOL's recommendation of 2048 in a 64-bit environment? (see my comment to Paul's answer, the BOL entry is this one: technet.microsoft.com/en-us/library/ms187024.aspx)
    – squillman
    Jun 30, 2009 at 4:50
  • And one further note regarding the schedulers - sys.dm_os_sys_info shows 4 for scheduler_count and 16 for scheduler_total_count.
    – squillman
    Jun 30, 2009 at 5:21
  • I don't see the discrepancy. The true default for Max Worker Threads is 0 which allows SQL Server to configure and manage the value for max workers based on the following calculation: ((NumberOfSchedulers - 4) * 8) + BaseThreadCount For 32 bit SQL Servers the BaseThreadCount is 256. For 64 bit SQL Servers the BaseThreadCount is doubled to 512. The table on the BOL link just shows the value of max workers if the default 0 is configured for various common configurations of CPU's. The recommended maximum is 2048 which would equate to 96 CPU's on the server using the default calculation. Jun 30, 2009 at 14:22
  • Ok, got it now. Thanks for setting me straight on that.
    – squillman
    Jun 30, 2009 at 14:38
2

See this entry on Books Online for how to change it: max worker threads Option and also see this discussion around increasing max worker threads from Ken Henderson's old blog. I'd be very wary of doing it unless absolutely necessary.

Hope this helps!

2
  • Thanks, Paul! There's an interesting comment to Ken's post about BOL contradicting the default settings for max_workers_count. There's a note in BOL that says recommended value for 64-bit is 2048. What it doesn't say, though, is for what processor config that's for. Would you be up for comment on that? I'm leaving well-enough alone on it, just curious to see what you thought of that bit.
    – squillman
    Jun 29, 2009 at 22:20
  • That I don't know I'm afraid as I'm not an expert on that area of SQL Server. Try commenting on Jonathan's answer below - he's more an expert there than I am. Jun 30, 2009 at 0:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .