Re: Worker Threads

Shane,

In order to use more than 4GB of memory you must use AWE. When you use AWE
memory is no longer dynamic and SQL Server will grab everything up to the
Max Memory limit for the memory pool and up to 384MB for MemToLeave. So I
find it hard to believe you are only using 2.7GB unless you do not have the
proper configurations. One is you must have Enterprise Edition of SQL
Server and at least Advanced Server Win2000. Another is you must have PAE
set in the Boot.ini and AWE enabled in SQL Server. The /3GB is optional with
this amount of memory but it looks like you have it on. I suggest you
double and triple check to ensure you have all of these correct as it does
not appear to be so. And when you do get it set properly you should drop
your max memory down to say 7.0 GB. A setting of 7.6GB with AWE will leave
only ~400MB for MemToLeave and the OS. This is usually not enough to ensure
proper operation under most conditions. Now as for the worker threads. Do
you know for a fact you are running out of them? Just because you have 1200
users does not mean you need that many worker threads. You actually require
much less and how many depends on how they are being used etc. If you are
not receiving errors stating you are out of worker threads I would leave it
alone.

--
Andrew J. Kelly SQL MVP


"Shane" <Shane@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:533AA171-63E1-4301-AC77-4D0FC1F543E8@xxxxxxxxxxxxxxxx
> Hi,
>
> I'm having performance issues on my SQL2000 box.
>
> We currently have a clustered Windows 2000 Microsoft SQL 2000 server. The
> node contains 8 processors and 8GB RAM. I have limited the SQL server to
> use
> 7.6Gb but is only currently using 2.7Gb.
>
> Current connections to the SQL box is between 1100 and 1200 and may
> decrease
> but not necessarily increase.
>
> The worker threads are still at the default of 255. I want to change it
> to
> 1000 but the articles from Microsoft do not give me much confidence.
>
> Has anybody else done any changes like this before and if so, what were
> the
> effects of this change.
>
> Any help would be greatly appreciated so that I don't make preformance
> worse
> than it already is.
>
> Cheers
>
> Shane


.

Relevant Pages

  • Re: Locking pages in memory
    ... So I think the 64-bit Standard does not support locked pages in memory. ... Rick Byham, SQL Server Books Online ...
    (microsoft.public.sqlserver.setup)
  • Re: Allocating more memory to sql server
    ... Are you sure the AWE was set correctly? ... > for SQL server Targer Sever memory and Total Server Memory, ... YOu should not use Task Manager to monitor memory for>> SQL Server. ... >>> windows task manager total physical memory shows as memory usage ...
    (microsoft.public.sqlserver.setup)
  • Re: Memory Question
    ... for SQL Server you must turn on AWE. ... If you turn on AWE you can use all of it but you then need to ... set the MAX Memory setting to about 7GB or so. ...
    (microsoft.public.sqlserver.setup)
  • Re: How to correctly use /PAE and AWE
    ... The /3GB gives SQL Server access to an extra GB of physical memory vs AWE ... >> Set Working Size configuration options how would I determine appropriate ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Server user 2GB> ram
    ... Because SQL Server does not grap that whole 6GB of memory as soon as you set it and Total Server Memory will show how much memory SQL Server consumes at that moment. ... I have enable the AWE using the following scripts: ...
    (microsoft.public.sqlserver.setup)