Re: AWE settings.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

AWE usage kills dynamic memory adjustment in SQL Server. The system immediately allocates memory at system startup. With your system settings, SQL will allocate 4.5 GB for the buffer pool, plus up to ~2.6 GB for internal operations with /3G engaged (~1.6 GB otherwise).

AWE allocations show up as PF committments due to Windows memory internals, so that is why you see the usage in perfmon and task manager. AWE in SQL requires locking pages in memory, so when you remove that ability from the service account, you are back to the 3GB limit.

It sounds like your system is running exactly as expected, given your configuration. I would not adjust any memory settings. I would monitor system free memory to see if any processes consume the remaining memory (replication tasks are notorious for slowly stealing memory).

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP





"Don" <Don@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:6603D565-D5F8-4E7F-82EC-33FCD46348CB@xxxxxxxxxxxxxxxx


Present Server configuration 32 bit.

4 cpus, 6 gb ram. WINDOWS 2003 enterprise with
service pack 1

Sql 2000 Enterprise edition Service pack 3

SQL configured for maxium server memory is 4.5 gb
with awe enabled.

The startup switches used are /3GB and /PAE.

Local Security Settings window/Local Policies/User
Rights
Assignment/Lock Pages in Memory is set for sql
server start up user
account.

ISSUE

O.S is showing low available memory like 200 MB

PF is 5.5 GB all the time.

Presently if we remove the setting Local Security
Settings window/Local
Policies/User Rights Assignment/Lock Pages in Memory
it shows around
3GB available memory. If this policy setting is
enabled the available
memory is too low around in the range of 140 MB to
260 MB.

There is no heavy load on the system and it is a
dedicated SQL server
box. Memory used by SQL server engine is around 1.6
GB most of the
time.

I was under the impression the sql server memory
usage is on demand
based upon the load and the memory configuration.
Looks like by enabling
this option in the policy it reserves more memory
and leaves very less
memory for the O.S.


If there is any article or document supporting if
the policy setting needs to be there will be of great
help.


Thanks in advance.


Don








.

Relevant Pages

  • 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: 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)
  • Re: FTS Performance in SQL 2005
    ... we had no end of problems with SQL FTS. ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: SQL Server user 2GB> ram
    ... AWE is the feature you need to make this work. ... The specific amount of memory you can use ... SQL Server will reserve almost all ... We have a Window Server 2003 EE and SQL 2000 EE Server with 32GB of Ram. ...
    (microsoft.public.sqlserver.setup)