Max Worker Threads and User Connections

Hi

we run SQL 2005 Enterprise on W2003 Enterprise 32 bit on 8 dual core Xeon 7020 with 32Gb RAM.

The max worker thread settings is 0 now. But 3d party consultancy which monitors our servers on the POP is suggesting to make this figure to be 1200. They said - there are 1000 connections at peak time. I am going to check if it is correct...

But my question is - is it proper thing to do to set worker thread number that high? (BOL says - MS doesnt recommend more than 1024 on 32 bit systems)

Is it that useful to match number of connections with number of threads?

Thank you

P.S. that consultancy let us down several times. I dont trust then at all.. I need a real reason to change such a thing

Well, I probably wouldn't recommend it generally speaking.

First off, just because there are 1000 connections at a given time, definately doesn't imply that there are 1000 concurrent processing requests...connections are frequently idle in many types of systems processing 'think' time by end users, or just sitting idle in a connection pool (.NET or otherwise). If you are using connection pooling, I'm sure you'll find that many of your connections are idle a fair amount of the time...

In a 32-bit system, on startup Sql server will reserve enough memory to load it's own binaries in, then some to handle 8kb and greater memory requests (typically 128mb by default, but can be modified using a startup switch), then enough memory to be able to allocate stacks for the total number of threads possible to use, which is determined by your 'max worker thread' setting.

Each thread stack is uses about .5mb of memory, which means using a setting of 1200 will result in a reserved space of about 600mb for just stack allocation on a 32bit box. Bear in mind that on a 32bit box of this size (assuming you aren't using a /3gb or /USERVA switch...if you are you ain't going to get to use most of your physical memory), the maximum VAS for a user-mode process like Sql server is 2gb, so stack space is going to use over a 1/4 of that space alone.

By default, in Sql 2005 on a 32bit box, Sql Server will dynamically handle threading using a simple algorithm where with 4 procs or less, 256 max threads will be used, then an additional 8 threads per CPU...in the case of a 16-way machine (yours is an 8-way dual core, 16 schedulers), Sql will use a max thread dynamic setting of 352 on that machine. If you bump that to 1200, your allocating about 850 more than the dyamic configuration would.

Given what I mentioned earlier about the fact that many connections are typically idle a fair amount of time, connections will pool the thread use at the OS level, and typically the dynamic default configuration is hightly recommend.

I'd ask the consultants to provide you extensive documentation on why they think it would be beneficial...typically, recommending something like this would take some extensive testing, and intimate knowledge of a system...

HTH,

|||

Thank you Chad,

I have same ideas but just needed some authoritative information to argue with the consulters.

I really hate 'solutions' made from thin air. I refused to do any changes and requested concrete measures have been made before they decided on the advice. I think they have none.

Might be there is a chance they are right (that advice was given to resolve issues I addressed in my previous topic: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=436762&SiteID=1). Fortunately these accidents havent repeated since I went through the application and put some NOLOCKs into critical places..