Maximun Worker Threads

Hello All,
I'm having performance problem with my database server:
My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
2003 Std Edition server, SQL 2000 Std Edition SP4.
I notice the slowness when there are more that 255 connections were made to
the SQL. Would it help if I increase the work threads?
Please Advise. Thanks.SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB of
memory MAX. So if you really have 8 dual core processors and 255 concurrent
connections I suggest you look at upgrading the edition and or at least the
version. Max worker threads is not your problem but what exactly is can not
be determined from this little bit of information.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David" <David@.discussions.microsoft.com> wrote in message
news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
> Hello All,
> I'm having performance problem with my database server:
> My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
> Windows
> 2003 Std Edition server, SQL 2000 Std Edition SP4.
> I notice the slowness when there are more that 255 connections were made
> to
> the SQL. Would it help if I increase the work threads?
> Please Advise. Thanks.|||Thanks for the reply.
Let me ask you this, SQL supports 4 "Sockets" but each sockets is a
dual-core would be 8 processors?
My question is Can I increase the number of maximun worker threads? I see
whenever it reach over maximun threads (255) I start to see the latency.
Thanks.
"Andrew J. Kelly" wrote:
> SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB of
> memory MAX. So if you really have 8 dual core processors and 255 concurrent
> connections I suggest you look at upgrading the edition and or at least the
> version. Max worker threads is not your problem but what exactly is can not
> be determined from this little bit of information.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
> > Hello All,
> >
> > I'm having performance problem with my database server:
> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
> > Windows
> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
> >
> > I notice the slowness when there are more that 255 connections were made
> > to
> > the SQL. Would it help if I increase the work threads?
> >
> > Please Advise. Thanks.
>|||From BOL:
The default setting for max worker threads (255) is best for most systems.
However, depending on your system configuration, setting max worker threads
to a smaller value sometimes improves performance.
When the actual number of user connections is less than the amount set in
max worker threads, one thread handles each connection. However, if the
actual number of connections exceeds the amount set in max worker threads,
SQL Server pools the worker threads so that the next available worker thread
can handle the request.
Yes, you can increase max worker threads. Most recommendations you will
find are to not mess with it. I would go up in small increments, and stay
way away from the upper limit of 32767. Also note that this setting take a
restart of sql server prior to the change taking effect.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David" <David@.discussions.microsoft.com> wrote in message
news:123172EF-8CB5-4C7A-9A59-2BDEBE529870@.microsoft.com...
> Thanks for the reply.
> Let me ask you this, SQL supports 4 "Sockets" but each sockets is a
> dual-core would be 8 processors?
> My question is Can I increase the number of maximun worker threads? I see
> whenever it reach over maximun threads (255) I start to see the latency.
> Thanks.
> "Andrew J. Kelly" wrote:
>> SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB
>> of
>> memory MAX. So if you really have 8 dual core processors and 255
>> concurrent
>> connections I suggest you look at upgrading the edition and or at least
>> the
>> version. Max worker threads is not your problem but what exactly is can
>> not
>> be determined from this little bit of information.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
>> > Hello All,
>> >
>> > I'm having performance problem with my database server:
>> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
>> > Windows
>> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
>> >
>> > I notice the slowness when there are more that 255 connections were
>> > made
>> > to
>> > the SQL. Would it help if I increase the work threads?
>> >
>> > Please Advise. Thanks.
>>|||> Would it help if I increase the work threads?
Collect the SQL wait stats, and check if there are a lot of waits on
ThreadPool. Also, watch perfmon counters under SQLServer:Wait Statistics\Wait
for the worker. In general, you shouldn't see any significant waits on the
worker at all.
Linchi
"David" wrote:
> Hello All,
> I'm having performance problem with my database server:
> My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
> 2003 Std Edition server, SQL 2000 Std Edition SP4.
> I notice the slowness when there are more that 255 connections were made to
> the SQL. Would it help if I increase the work threads?
> Please Advise. Thanks.|||Can you increase this, yes of course you can. But you need to determine that
adding more threads will actually help. If you have a lot of waits on the
threads it can be a sign you need more. But I am willing to bet that your
system as configured can not adequately handle that many threads. If the
system (both hardware and database) was properly configured and tuned you
would most likely never need more than 255 worker threads. Try bumping it
and see if it helps but I would start to tune the system or think about
moving to SQL2005 so you can use more memory effectively.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David" <David@.discussions.microsoft.com> wrote in message
news:123172EF-8CB5-4C7A-9A59-2BDEBE529870@.microsoft.com...
> Thanks for the reply.
> Let me ask you this, SQL supports 4 "Sockets" but each sockets is a
> dual-core would be 8 processors?
> My question is Can I increase the number of maximun worker threads? I see
> whenever it reach over maximun threads (255) I start to see the latency.
> Thanks.
> "Andrew J. Kelly" wrote:
>> SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB
>> of
>> memory MAX. So if you really have 8 dual core processors and 255
>> concurrent
>> connections I suggest you look at upgrading the edition and or at least
>> the
>> version. Max worker threads is not your problem but what exactly is can
>> not
>> be determined from this little bit of information.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
>> > Hello All,
>> >
>> > I'm having performance problem with my database server:
>> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
>> > Windows
>> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
>> >
>> > I notice the slowness when there are more that 255 connections were
>> > made
>> > to
>> > the SQL. Would it help if I increase the work threads?
>> >
>> > Please Advise. Thanks.
>>|||Linchi-
SQLServer:Wait Statistic only appears in SQL2005. Where can I find it in
SQL2000?
Thanks.
"Linchi Shea" wrote:
> > Would it help if I increase the work threads?
> Collect the SQL wait stats, and check if there are a lot of waits on
> ThreadPool. Also, watch perfmon counters under SQLServer:Wait Statistics\Wait
> for the worker. In general, you shouldn't see any significant waits on the
> worker at all.
> Linchi
> "David" wrote:
> > Hello All,
> >
> > I'm having performance problem with my database server:
> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
> >
> > I notice the slowness when there are more that 255 connections were made to
> > the SQL. Would it help if I increase the work threads?
> >
> > Please Advise. Thanks.|||Oops! I didn't realize you were using SQL2000. In that case, just collect the
SQL wait stats via dbcc sqlperf(waitstats).
Linchi
"David" wrote:
> Linchi-
> SQLServer:Wait Statistic only appears in SQL2005. Where can I find it in
> SQL2000?
> Thanks.
> "Linchi Shea" wrote:
> > > Would it help if I increase the work threads?
> >
> > Collect the SQL wait stats, and check if there are a lot of waits on
> > ThreadPool. Also, watch perfmon counters under SQLServer:Wait Statistics\Wait
> > for the worker. In general, you shouldn't see any significant waits on the
> > worker at all.
> >
> > Linchi
> >
> > "David" wrote:
> >
> > > Hello All,
> > >
> > > I'm having performance problem with my database server:
> > > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
> > > 2003 Std Edition server, SQL 2000 Std Edition SP4.
> > >
> > > I notice the slowness when there are more that 255 connections were made to
> > > the SQL. Would it help if I increase the work threads?
> > >
> > > Please Advise. Thanks.

Maximize memory usage on active-active sql cluster

we have an active-active sql cluster configured as follow:
server A - 4gb ram - sql server instance #1
server B - 4gb ram - sql server instance #2
currently each sql instance is configured to use at most 2gb of ram.
in case of failover, both instances switch on one server, effectively
usinge all 4gb without paging.
but it's quite a waste. under normal condition each server is not
using 2gb.
one option we are evaluating is configuring each server to use
min=1gb and max=3gb. and _disabling_ tha paging file.
disabling tha paging file should overcome the problem that, in case of
failover, the two instaces will start paging effectively rendering the
server unusable.
do you think it could work?
do you have any other suggestions?
thanks in advance,
cheers
/ettore
Hi
If you disable the paging file (which you can't as there is always some
kernel code paged out), then the other instance could not fail over as there
would be no RAM for it. So a failover cluster would be useless.
With the /3GB switch, user addressable memory limit is 3GB. The OS keeps 1Gb
for itself. Even with 2Gb, you are over committing memory. 1.5Gb maximum
should be your limit per instance to cater for failover.
Regards
Mike
"Ettore Pancini" wrote:

> we have an active-active sql cluster configured as follow:
> server A - 4gb ram - sql server instance #1
> server B - 4gb ram - sql server instance #2
> currently each sql instance is configured to use at most 2gb of ram.
> in case of failover, both instances switch on one server, effectively
> usinge all 4gb without paging.
> but it's quite a waste. under normal condition each server is not
> using 2gb.
> one option we are evaluating is configuring each server to use
> min=1gb and max=3gb. and _disabling_ tha paging file.
> disabling tha paging file should overcome the problem that, in case of
> failover, the two instaces will start paging effectively rendering the
> server unusable.
> do you think it could work?
> do you have any other suggestions?
> thanks in advance,
> cheers
> /ettore
>
|||thanks for answering Mike,
so, let's forget about disable paging. i understand it wont work.
and, thanks for pointing out the /3GB switch stuff wich I wasn't aware
of.
but, from BoL, reading from this page:
Dynamically Managing Memory Between Multiple Instances
exactly from this sentence "Once the amount of memory reaches the
point where only 4 MB to 10 MB is free, the instances begin competing
with each other for memory." and forward on. it seems that when in
competition, instances could free memory based on workload.
so suppose this scenario:
- instance configures as min=1.5GB, max=3.0GB
- normally each intance expand their usage to 3gb
- then one node fails
- instance wich was on the failing node is moved
- the two instances start competing
- the other instance starts to free memory
do you think it could work?
regarding paging, in the same article, BoL states that the dynamic
memory management algorithm will "ensures that the overall amount of
allocated memory remains under the level that would generate paging"
cheers, Ettore

> If you disable the paging file (which you can't as there is always some
> kernel code paged out), then the other instance could not fail over as there
> would be no RAM for it. So a failover cluster would be useless.
> With the /3GB switch, user addressable memory limit is 3GB. The OS keeps 1Gb
> for itself. Even with 2Gb, you are over committing memory. 1.5Gb maximum
> should be your limit per instance to cater for failover.
> Regards
> Mike
>
|||That's true, they can both live on the same node and compete for the
same 3GB of memory (one of my clusters is doing that at the moment in
fact). However, if they're both fairly active instances then data pages
will get swapped in and out of memory more often and the average page
life expectancy will be substantially reduced. My 2 instances are
normally fairly sedate in terms of memory allocation when on separate
nodes. At the moment they're allocating & deallocating pages in memory
like there's no tomorrow.
That's good if what you're after is to use all of your memory all of the
time. But if you're after a nice stable buffer cache then it would be
better not having them compete with each other for the same RAM.
Note bene, if you enable AWE memory in SQL (to allow a virtual address
space of more than 4GB for SQLServer; remember the kernel address space
is alway 1 or 2GB depending upon whether you enable /3GB or not) then
dynamic memory management is automatically turned off. This is because
to use physical memory above 4GB the process needs to lock pages in
memory and cannot swap pages out to disk. This means that SQL Server's
dynamic memory management goes out the window and you have to set a max
server memory limit for all instances that may reside on that node
(presumedly all nodes in the cluster have the same amount of physical
RAM) and the sum of those max server memory settings should be less than
the total physical RAM in presented to the O/S.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Ettore Pancini wrote:
[vbcol=seagreen]
>thanks for answering Mike,
>so, let's forget about disable paging. i understand it wont work.
>and, thanks for pointing out the /3GB switch stuff wich I wasn't aware
>of.
>but, from BoL, reading from this page:
>Dynamically Managing Memory Between Multiple Instances
>exactly from this sentence "Once the amount of memory reaches the
>point where only 4 MB to 10 MB is free, the instances begin competing
>with each other for memory." and forward on. it seems that when in
>competition, instances could free memory based on workload.
>so suppose this scenario:
>- instance configures as min=1.5GB, max=3.0GB
>- normally each intance expand their usage to 3gb
>- then one node fails
>- instance wich was on the failing node is moved
>- the two instances start competing
>- the other instance starts to free memory
>do you think it could work?
>regarding paging, in the same article, BoL states that the dynamic
>memory management algorithm will "ensures that the overall amount of
>allocated memory remains under the level that would generate paging"
>cheers, Ettore
>
>