i am facing the following issue.

         maximum number of working threads (400) are already running.
I have 4 logical cpu's(64 bit),4GB RAM .even after i increased the max number of working threads to 1024 i am facing the same problem.Please give me some suggestions on this.
more ▼

asked May 16 '12 at 06:20 AM in Default

chinna gravatar image

10 2 5 5

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
The recommended value for max worker threads is 0 which 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. But this does not prove good in your case. Also, after changing the max worker threads values, you need to restart the sytem for the new setting to take effect.
more ▼

answered May 16 '12 at 10:44 AM

robbin gravatar image

1.6k 1 3 5

(comments are locked)
10|1200 characters needed characters left
is it possible to increase(more than 400) max worker threads for my system configuration and can you tell me how memory is related to max worker threads.i restarted my system after changing the max worker threads.
more ▼

answered May 17 '12 at 04:28 AM

chinna gravatar image

10 2 5 5

Yes it is possible to add more than 400. As per BOL, for 64 bit, <= 4 processors 512 max value is autoconfigured.

Setting the max worker threads option to a small value may prevent enough threads from servicing incoming client requests in a timely manner and could lead to "thread starvation". However, setting the option to a large value can waste address space, because each active thread consumes 512 KB on 32-bit servers and up to 4 MB on 64-bit servers. I hope this will help you understand the relationship b/w Worker threads and Memory.
May 17 '12 at 11:31 AM robbin
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 16 '12 at 06:20 AM

Seen: 2751 times

Last Updated: May 17 '12 at 11:31 AM