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
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
|||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[vbcol=seagreen]
> 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...
Threads,[vbcol=seagreen]
> but
around
> 350?
>
|||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...[vbcol=seagreen]
> 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...
Threads,[vbcol=seagreen]
around
>
|||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...
>
>
|||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...[vbcol=seagreen]
> 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:
No comments:
Post a Comment