Re: Memory configuration for multiple instances and AWE

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

Why do you want 4 instances instead of 1 instance with all the dbs in that
one instance? On 32 bit OS with AWE turned on in SQL Server it can get
tricky to manage the memory. First off AWE memory is not dynamic. If you set
Max memory to 4GB then you will get 4GB and no one else will touch it. If
you use 1 instance even though the memory is still not dynamic it is shared
amongst all the dbs.

--
Andrew J. Kelly SQL MVP

"Joseph P." <JosephP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1E899300-77ED-4DAB-B7CD-5649D062A138@xxxxxxxxxxxxxxxx
Hi All,
I have a question regarding AWE and memory configuration for multiple
instances.

Configuration that I have on the server is like this (this is SQL box
only,
no other applications will be running on this box):

For OS I am running Windows Server 2003 Enterprise edition with SP2.

I have 4 instances of SQL Server 2005 Standard edition SP2 installed. One
of
those four instances is hosting MOM reporting database
(SystemCenterReporting
DB) and SQL reporting. At a moment only default instance and one that is
hosting MOM reporting DB have active databases, but other two will get
some
load very soon.

RAM memory is 16 GB.

Disk storage is on the SAN and I have two mounted volumes one for data and
one for the log files.

I have /PAE switch in the Windows Boot.INI.
I granted LOCK PAGE IN MEMORY to the SQL Server account.
I enabled AWE for all SQL instances and I have set min SQL memory to 1GB
and
max memory to 4GB as a temporary solution for two active instances, but
somehow thinking this is not the best configuration possible.
Instance running MOM Reporting and default instance will be the most
active.

My question is - what is the best way to configure memory for all four
instances?

Cheers,

Joseph



.

Relevant Pages

  • Re: AWE settings.
    ... dynamic AWE to work using SQL 2005, but don't quote me on that. ... Microsoft SQL Server MVP ... immediately allocates memory at system startup. ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Server 2000 AWE on Windows 2003 64bit
    ... Since Chris is enabling the AWE in SQL Server 2000 under Windows ... How is the SQL Server memory management is going to work? ...
    (microsoft.public.sqlserver.clustering)
  • RE: 2003 & SQL 2000 Clustering Memory
    ... I will use AWE with memory capped on SQL as per your advice. ... I will enable PAE. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Allocating more memory to sql server
    ... Are you sure you have Windows 2000 Advanced Server and not just Server? ... SQL Server. ... memory used by SQL Server that will give a better idea of the actual usage. ... You also need to restart SQL Server after enabling AWE for it to take ...
    (microsoft.public.sqlserver.setup)
  • Re: AWE settings.
    ... IIRC, you have to be on Windows Server 2003 R2 for dynamic AWE to work using SQL 2005, but don't quote me on that. ... Then there is 64-bit which works completely differently with memory access since it can count higher than 4GB. ...
    (microsoft.public.sqlserver.setup)