define Hierarchy: ram

Showing posts with label ram. Show all posts

Wednesday, March 28, 2012

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

How to determine the best setting for Max Worker Threads

"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:
>|||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 wil
l
> 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...
> but
> 350?
>
>|||> 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...[vbcol=seagreen]
> How do you know how many connections so that i can monitor this too.
> thanks
> "Wayne Snyder" wrote:
>|||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...
>
>

How to determine the best setting for Max Worker Threads

"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:

Friday, March 23, 2012

How to determine how much RAM my SQL server is actually using??

I've been doing a lot of reading on this and my head is starting to
hurt! It seems to be quite a feat to work out how much memory is
actually being used by our server.

I'm running W2K advanced server with SQL 2000 EE, 8GB of RAM, a min of
4GB and a max of 6GB is assigned to SQL server.

I'm trying to work out whether we've assigned enough or too
much/little memory to SQL server. My first thought was to let SQL
dymanically manage its own memory and see how much it uses, of course
when AWE (/3GB /PAE) is enabled it will just use all that is
available.

In perfmon "target server memory" = 6.1GB, "total server memory" =
6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).

My second thought was to use "total pages" - the average "free pages"
= average mem used, therefore giving me the average amount of memory
used by SQL. I found out that SQL uses a min of 4GB (the min we
assigned) and the max of all the memory, 6GB.

Is there an easier way of finding out how much memory is actually used
in this situation or is going by the above average the best way?

What i'm unsure about is will SQL just use all memory assigned to it
until it has the whole DB in memory? 20GB including indexes etc...

Any help would be greatly apprechiated.tim@.specialmail.co.uk (Tim Richardson) wrote in message news:<25879432.0410200316.75783fee@.posting.google.com>...
> I've been doing a lot of reading on this and my head is starting to
> hurt! It seems to be quite a feat to work out how much memory is
> actually being used by our server.
> I'm running W2K advanced server with SQL 2000 EE, 8GB of RAM, a min of
> 4GB and a max of 6GB is assigned to SQL server.
> I'm trying to work out whether we've assigned enough or too
> much/little memory to SQL server. My first thought was to let SQL
> dymanically manage its own memory and see how much it uses, of course
> when AWE (/3GB /PAE) is enabled it will just use all that is
> available.
> In perfmon "target server memory" = 6.1GB, "total server memory" =
> 6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).
> My second thought was to use "total pages" - the average "free pages"
> = average mem used, therefore giving me the average amount of memory
> used by SQL. I found out that SQL uses a min of 4GB (the min we
> assigned) and the max of all the memory, 6GB.
> Is there an easier way of finding out how much memory is actually used
> in this situation or is going by the above average the best way?
> What i'm unsure about is will SQL just use all memory assigned to it
> until it has the whole DB in memory? 20GB including indexes etc...
> Any help would be greatly apprechiated.

Looks like everyone is just as stumped as me!! Could really do with
some help on this one!|||Tim Richardson (tim@.specialmail.co.uk) writes:
> tim@.specialmail.co.uk (Tim Richardson) wrote in message
news:<25879432.0410200316.75783fee@.posting.google.com>...
>> I'm trying to work out whether we've assigned enough or too
>> much/little memory to SQL server. My first thought was to let SQL
>> dymanically manage its own memory and see how much it uses, of course
>> when AWE (/3GB /PAE) is enabled it will just use all that is
>> available.
>>
>> In perfmon "target server memory" = 6.1GB, "total server memory" =
>> 6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).
>>
>> My second thought was to use "total pages" - the average "free pages"
>> = average mem used, therefore giving me the average amount of memory
>> used by SQL. I found out that SQL uses a min of 4GB (the min we
>> assigned) and the max of all the memory, 6GB.
>>
>> Is there an easier way of finding out how much memory is actually used
>> in this situation or is going by the above average the best way?
>>
>> What i'm unsure about is will SQL just use all memory assigned to it
>> until it has the whole DB in memory? 20GB including indexes etc...

With 6MB as the max limit, it seems unlikely that SQL Server would be
able to fit 20 GB into the cache. To do that it would need at least
21 GB, with the rough assumption that you need 1 GB for the query plans
and everything else.

But I will have to admit that this is not my best game, not the least
when you bring in that much of memory. Some of my MVP colleagues know
this kind of stuff a lot better. Have you tried posting to
microsoft.public.sqlserver.server?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 28, 2012

How to determine the best setting for Max Worker Threads

How to determine the best setting for Max Worker Threads

How to determine the best setting for Max Worker Threads

Friday, March 23, 2012

How to determine how much RAM my SQL server is actually using??

define Hierarchy

Blog Archive

About Me