Couple of questions

1) To use or not to use the 3GB switch. We have 8GB of RAM and just run SQL
Server with awe and PAE Enabled. I have heard that 3GB is good and I have
also heard 3 GB is bad with the above setup..
2) We have a 32 bit system and we run into worker thread issues such all 256
worker threads are used and all incoming connections are rejected. Should I
be increasing those worker threads to say 512 ?
3) What is stored in the address space above the 4GB limit ..i.e when i use
awe to address say 6GB , is it only data/index pages that reside in that
region of memory above the 4GB limit ?
4) Is user connection memory, procedure cache stored in mem to leave area ?
We are using SQL 2000 EE
ThanksAnswers Inline
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%2300axugfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> 1) To use or not to use the 3GB switch. We have 8GB of RAM and just run
> SQL Server with awe and PAE Enabled. I have heard that 3GB is good and I
> have also heard 3 GB is bad with the above setup..
>
3GB is usually OK up to 12 GB total system RAM. Above that it is not
recommended

> 2) We have a 32 bit system and we run into worker thread issues such all
> 256 worker threads are used and all incoming connections are rejected.
> Should I be increasing those worker threads to say 512 ?
How many processers do you have? Are they running above 90% at the time of
the incoming rejections?

> 3) What is stored in the address space above the 4GB limit ..i.e when i
> use awe to address say 6GB , is it only data/index pages that reside in
> that region of memory above the 4GB limit ?
>
AWE memory is not addressable and is used for data cache only.

> 4) Is user connection memory, procedure cache stored in mem to leave area
> ?
No, with one exception. If you are using large packets, then user
connection memory may come out of mem-to-leave if the system has to use OS
network buffers rather than SQL network buffers.

> We are using SQL 2000 EE
> Thanks
>
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP|||Thanks Geoff..
1) So what are we missing out on for not using 3GB if we have less than 12GB
of RAM ?
2) We have seen worker thread problems on our high OLTP systems. Some
possible causes could be h/w related such as disk IO issue causing slowness
in response times all revolving around blocking..So say if the blocking
sproc runs for 10 secs all of a sudden, but there are around 1000 sprocs
that want to execute and read of that blocked table, then in no time, we run
out of threads and connections are not honored.
3) With AWE , does data flow from the non addressable memory to addressable
memory ?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ehrOAIhfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Answers Inline
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%2300axugfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> 3GB is usually OK up to 12 GB total system RAM. Above that it is not
> recommended
>
> How many processers do you have? Are they running above 90% at the time
> of the incoming rejections?
>
> AWE memory is not addressable and is used for data cache only.
>
> No, with one exception. If you are using large packets, then user
> connection memory may come out of mem-to-leave if the system has to use OS
> network buffers rather than SQL network buffers.
>
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>|||More Inline
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23dYzJghfGHA.1456@.TK2MSFTNGP04.phx.gbl...
> Thanks Geoff..
> 1) So what are we missing out on for not using 3GB if we have less than
> 12GB of RAM ?
>
/3GB allows for more SQL addressable memory. Depending on your system, this
may improve performance. As with any config change, test both before and
after to see if it improves things. In most cases with system memory above
4GB and less than or equal to 12GB, you will see an improvement.

> 2) We have seen worker thread problems on our high OLTP systems. Some
> possible causes could be h/w related such as disk IO issue causing
> slowness in response times all revolving around blocking..So say if the
> blocking sproc runs for 10 secs all of a sudden, but there are around 1000
> sprocs that want to execute and read of that blocked table, then in no
> time, we run out of threads and connections are not honored.
>
Monitor your current disk queue length counters for your data and log disks.
If you are having IO stalls, you will need to improve the IO subsystem
before any worker thread count improvements will help. This is why I
hesitate to recommend a specific change over the newsgroups. You need to
find the root cause of your performance issues. Sometimes one issue, such
as an I/O weakness, can show up as memory or CPU pressure to casual
observation.

> 3) With AWE , does data flow from the non addressable memory to
> addressable memory ?
>
That is why you have to limit the use of the /3GB switch. AWE is much like
the old EXTENDED and EXPANDED RAM from the '268 days where a window of low
(or in this case addressable) memory is used to map into the non-addressable
memory. This is done via the OS memory area, so if you squeeze it too much
on very large memory systems, you introduce some severe performance issues.
The OS needs the extra memory to handle to address windows.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:ehrOAIhfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>