How to determine the best setting for Max Worker Threads

Dear All,
I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
environment. Right now, I am using the default 255 on Max Worker Threads, but
based on my analysis, during peak hour, the number of connection is around
300, is that better to set the max worker threads to a higher value e.g. 350?
What will happen with I set it to 510 instead? Many thanks!
From,
HenryEach thread takes a small amount of memory, even if it is not being used. I
don'tremember how much ( maybe 1k). The best practice is NOT to set it
arbitrarily high... In your case, I'd set it to 300 or 310. and watch for
max connections occasionally. or alert on max connections > 310 so you will
know if you need to bump it again...
If these are not heads-down data entry people, it may even be OK for them to
thread share...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> Dear All,
> I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> environment. Right now, I am using the default 255 on Max Worker Threads,
but
> based on my analysis, during peak hour, the number of connection is around
> 300, is that better to set the max worker threads to a higher value e.g.
350?
> What will happen with I set it to 510 instead? Many thanks!
> From,
> Henry|||IIRC, each worker thread uses closer to 384k of RAM.
I agree with Wayne. Use the 255 right now and use System Monitor and/or
Profiler and get a baseline value. Then bump it up to around 300 and check
the new values against your baseline. With a little tweaking here and
there, you should be able to come up with a reasonable value.
Rick Sawtell
MCT, MCSD, MCDBA
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OGiSzYesEHA.3580@.TK2MSFTNGP10.phx.gbl...
> Each thread takes a small amount of memory, even if it is not being used.
I
> don'tremember how much ( maybe 1k). The best practice is NOT to set it
> arbitrarily high... In your case, I'd set it to 300 or 310. and watch for
> max connections occasionally. or alert on max connections > 310 so you
will
> know if you need to bump it again...
> If these are not heads-down data entry people, it may even be OK for them
to
> thread share...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker
Threads,
> but
> > based on my analysis, during peak hour, the number of connection is
around
> > 300, is that better to set the max worker threads to a higher value e.g.
> 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>|||Just because ht enumber of connection is over 255 does not in any way mean
you need more worker threads. Take a look at
dbcc sqlperf(UMSSTATS) and see how they are being used now.
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> Dear All,
> I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> environment. Right now, I am using the default 255 on Max Worker Threads,
> but
> based on my analysis, during peak hour, the number of connection is around
> 300, is that better to set the max worker threads to a higher value e.g.
> 350?
> What will happen with I set it to 510 instead? Many thanks!
> From,
> Henry|||Oops...
Thanks Andrew... I meant connections, not worker threads.
In fact, if I remember correctly, we demo'd a 4 processor SQL 7.0 instance a
number of years ago and found that tuning the worker threads down to
somewhere around 12 gave us the best performance...
Rick
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OLaR4thsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Just because ht enumber of connection is over 255 does not in any way mean
> you need more worker threads. Take a look at
> dbcc sqlperf(UMSSTATS) and see how they are being used now.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker
Threads,
> > but
> > based on my analysis, during peak hour, the number of connection is
around
> > 300, is that better to set the max worker threads to a higher value e.g.
> > 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>|||Dear Andrew,
Thanks for your advise. Would you please kindly explain how to use dbcc
sqlperf(UMSSTATS) to determine the best setting? Coz as far as I know from
web article, we can use the "num runnable" to determine whether there is
bottleneck on CPU, if always 0, then is healthy, otherwise, may have
bottleneck. For my case, the current default worker thread setting 255, give
me 0 or 1 most of the time. Is that my goal is to adjust until the "num
runnable" always on 0? Please advise, many thanks!
From,
Henry :)
"Andrew J. Kelly" wrote:
> Just because ht enumber of connection is over 255 does not in any way mean
> you need more worker threads. Take a look at
> dbcc sqlperf(UMSSTATS) and see how they are being used now.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker Threads,
> > but
> > based on my analysis, during peak hour, the number of connection is around
> > 300, is that better to set the max worker threads to a higher value e.g.
> > 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>
>|||This is a sample output for the UMSSTATS. Your question is whether you
should bump up the max worker threads. The default is a maximum of 255 and
they get evenly divided between the processors assigned to SQL Server. In
your case that is 4 processors and that means the number of workers for each
processor can be over 60. But it will rarely get even close to that on a
system that is not extremely busy. The key is to look at the "idle workers"
size in realation to the "num workers". In your case if the value is always
0 or 1 you are hardly pushing the systems ability to work with what it has.
You are seeing normal behavior.
Scheduler ID 0.0
num users 18.0
num runnable 0.0
num workers 13.0
idle workers 11.0
work queued 0.0
cntxt switches 2.2994396E+7
cntxt switches(idle) 1.7793976E+7
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:E13223AE-6F93-4D5E-8082-472E73FA6585@.microsoft.com...
> Dear Andrew,
> Thanks for your advise. Would you please kindly explain how to use dbcc
> sqlperf(UMSSTATS) to determine the best setting? Coz as far as I know from
> web article, we can use the "num runnable" to determine whether there is
> bottleneck on CPU, if always 0, then is healthy, otherwise, may have
> bottleneck. For my case, the current default worker thread setting 255,
> give
> me 0 or 1 most of the time. Is that my goal is to adjust until the "num
> runnable" always on 0? Please advise, many thanks!
> From,
> Henry :)
> "Andrew J. Kelly" wrote:
>> Just because ht enumber of connection is over 255 does not in any way
>> mean
>> you need more worker threads. Take a look at
>> dbcc sqlperf(UMSSTATS) and see how they are being used now.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Henry" <Henry@.discussions.microsoft.com> wrote in message
>> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
>> > Dear All,
>> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
>> > environment. Right now, I am using the default 255 on Max Worker
>> > Threads,
>> > but
>> > based on my analysis, during peak hour, the number of connection is
>> > around
>> > 300, is that better to set the max worker threads to a higher value
>> > e.g.
>> > 350?
>> > What will happen with I set it to 510 instead? Many thanks!
>> > From,
>> > Henry
>>|||How do you know how many connections so that i can monitor this too.
thanks
"Wayne Snyder" wrote:
> Each thread takes a small amount of memory, even if it is not being used. I
> don'tremember how much ( maybe 1k). The best practice is NOT to set it
> arbitrarily high... In your case, I'd set it to 300 or 310. and watch for
> max connections occasionally. or alert on max connections > 310 so you will
> know if you need to bump it again...
> If these are not heads-down data entry people, it may even be OK for them to
> thread share...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker Threads,
> but
> > based on my analysis, during peak hour, the number of connection is around
> > 300, is that better to set the max worker threads to a higher value e.g.
> 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>
>|||> How do you know how many connections so that i can monitor this too.
> thanks
You can monitor User Connections using Performance Monitor. You can also
get the current number of user connections with the following query:
SELECT cntr_value
FROM master..sysperfinfo
WHERE
object_name = 'SQLServer:General Statistics' AND
counter_name = 'User Connections'
Keep in mind that threads are pooled so you can have thousands of database
connections without the need to increase max worker threads. Connections
are often waiting for work and are not using a thread. Personally, I
wouldn't consider increasing the default unless the 'working thread limit of
255 has been reached' in appears in the error log.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TRACEY" <TRACEY@.discussions.microsoft.com> wrote in message
news:AFDCBDA1-9559-40E6-97A6-B0F52053920C@.microsoft.com...
> How do you know how many connections so that i can monitor this too.
> thanks
> "Wayne Snyder" wrote:
>> Each thread takes a small amount of memory, even if it is not being used.
>> I
>> don'tremember how much ( maybe 1k). The best practice is NOT to set it
>> arbitrarily high... In your case, I'd set it to 300 or 310. and watch
>> for
>> max connections occasionally. or alert on max connections > 310 so you
>> will
>> know if you need to bump it again...
>> If these are not heads-down data entry people, it may even be OK for them
>> to
>> thread share...
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "Henry" <Henry@.discussions.microsoft.com> wrote in message
>> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
>> > Dear All,
>> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
>> > environment. Right now, I am using the default 255 on Max Worker
>> > Threads,
>> but
>> > based on my analysis, during peak hour, the number of connection is
>> > around
>> > 300, is that better to set the max worker threads to a higher value
>> > e.g.
>> 350?
>> > What will happen with I set it to 510 instead? Many thanks!
>> > From,
>> > Henry
>>|||So if i run the dbcc sqlperf(umsstats) command say every hour i see how many
threads are in progress at any time......if reaching 255 at any one time
then i look at whether or not to change it
Thanks i have a look
"Andrew J. Kelly" wrote:
> Just because ht enumber of connection is over 255 does not in any way mean
> you need more worker threads. Take a look at
> dbcc sqlperf(UMSSTATS) and see how they are being used now.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker Threads,
> > but
> > based on my analysis, during peak hour, the number of connection is around
> > 300, is that better to set the max worker threads to a higher value e.g.
> > 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>
>sql