configuring the number of worker threads

We have a SQL 2000 server with max worker threads configured to be
3232767275275
The server sees some pretty high usage and we've been seeing some slow down
in the server. I've read that most systems run well with a value of 255.
I've also read something that suggests that lowering this value may help. Is
it better to lower the value or increase it to help performance?
Thanks
MG
Hi,
The default value set is 255 and is more than enough.
Most cases it is not required to increase the value; if the actual number
of connections exceeds the amount set in threads, SQL Server does a pooling
for worker threads so that the next available worker thread can handle the
request.
From Query anayzer run the command to get the correct value
SP_CONFIGURE 'max worker threads'
From the output see the running value. If the value is higher than 255 try
to change and monitor the threads using performance montor, Process, Thread
counter. This will give a clear picture of thread usage.
Thanks
Hari
SQL Server MVP
"MGeles" <michael.geles@.thomson.com> wrote in message
news:608FFF4F-87C3-4011-AB16-6C4412A23870@.microsoft.com...
> We have a SQL 2000 server with max worker threads configured to be
> 32 32767 275 275
> The server sees some pretty high usage and we've been seeing some slow
> down
> in the server. I've read that most systems run well with a value of 255.
> I've also read something that suggests that lowering this value may help.
> Is
> it better to lower the value or increase it to help performance?
> Thanks
> --
> MG
|||See http://support.microsoft.com/default...b;en-us;319942 for
additional information.
Adrian
"MGeles" <michael.geles@.thomson.com> wrote in message
news:608FFF4F-87C3-4011-AB16-6C4412A23870@.microsoft.com...
> We have a SQL 2000 server with max worker threads configured to be
> 32 32767 275 275
> The server sees some pretty high usage and we've been seeing some slow
> down
> in the server. I've read that most systems run well with a value of 255.
> I've also read something that suggests that lowering this value may help.
> Is
> it better to lower the value or increase it to help performance?
> Thanks
> --
> MG
|||MGeles,
This is a very small part of a much wider area of performance tuning. Have a
look through the articles at www.sql-server-performance.com for some
excellent methods on tuning your server.
You need to ascertain in a nutshell:
1) Is there any blocking happening > 1 sec
2) Is the disk IO bound
3) Is the server CPU bound
4) Which queries slow down the server
5) How much paging is happening in windows
6) How's the sql server buffer cache hit ratio - indicates RAM starvation
Also check your query plans and make sure your indexing is optimal. A
technique I use is to trace a typical workload into a SQL server table and
then query the trace table for the most cpu, io intensive and longest running
queries. I then check the query plans for these queries, and also the code.
You may even want to look at the database design itself - the biggest
performance bangs for your buck can often be obtained through tuning your
database design.
Regards,
Mark.
"MGeles" wrote:

> We have a SQL 2000 server with max worker threads configured to be
> 3232767275275
> The server sees some pretty high usage and we've been seeing some slow down
> in the server. I've read that most systems run well with a value of 255.
> I've also read something that suggests that lowering this value may help. Is
> it better to lower the value or increase it to help performance?
> Thanks
> --
> MG