Max worker threads

Does anyone use more than 256 worker threads ? | keyongtech

Does anyone use more than 256 worker threads ? | keyongtech — Thu, 22 Jan 2009 08:06:34 GMT

PingBack from http://www.keyongtech.com/2232298-does-anyone-use-more-than

Increasing Max worker threads - A negative impact « Duty Free!! The World Logs here…

Wed, 21 Jan 2009 08:48:31 GMT

PingBack from http://honeyvirus.wordpress.com/2009/01/21/increasing-max-worker-threads-a-negative-impact/

re: Max worker threads

Tim — Fri, 07 Sep 2007 20:12:24 GMT

Ken,

While I love your books I can't reconcile between your post above saying to not change this setting when others at Microsoft say to do this. See the technet article http://technet.microsoft.com/en-us/library/ms187024.aspx In it, they state:

The default value for max worker threads, 0, allows SQL Server to automatically configure the number of worker threads at startup. This setting is best for most systems; however, depending on your system configuration, setting max worker threads to a specific value sometimes improves performance.

The following table shows the automatically configured number of max worker threads for various combinations of CPUs and versions of SQL Server.

Number of CPUs 32-bit computer 64-bit computer

<= 4 processors 256 512

8 processors 288 576

16 processors 352 704

32 processors 480 960

Note:

We recommend 1024 as the maximum for 32-bit SQL Server and 2048 for 64-bit SQL Server.

This leaves me confused as I have a SS2K server with 12 itanium processor with 64 GB of memory along with 1500 connections into the database and a consultant who says bump the number of Max worker threads up. Who am I to believe?

Can I use 0 for Max Worker Threads on a SQL Server 2000 database and let it manage everything?

Tim

re: Max worker threads

Pat — Wed, 05 Sep 2007 14:40:51 GMT

Do you concur with Joe's findings... ? thanks.

re: Max worker threads

Joe — Tue, 08 Aug 2006 00:44:29 GMT

Ken,

In high volume, short transaction time applications (especially those that use client-side transactions or distributed transactions), we've found that if max_worker_threads is set too low then you start getting hung schedulers (17883 errors, etc.). This gets exacerbated on multiprocessor boxes because any individual scheduler needs a shorter blocking chain to put it at risk for a hung scheduler condition. I agree that increasing max_worker_threads has no impact on performance (or even a negative impact), but the fact is that with the given scheduler architecture in SQL 2000 you have to sacrifice some throughput to guarantee server stability (enhance maybe a better word than guarantee - even with higher worker thread settings you can still get occasionally 17883 errors, which makes your customers start screaming for Oracle).

Joe