How to set memory on server

I have SQL 2000 server with two instances installed on the
box. The server has 4 GB RAM.
(1)How can I choose SQL server memory settings to obtain
better performance for both instances?
(2)Should I choose "Dynamic" setting or "Fixed memory"
setting?
Thanks in advance.
DonActually, there is no direct answer for this. It depends
on how much memory is required for SQL Server and
applications using this sql server.
It also depends on any other applications running on the
same box.
In General, You have to allocate for OS,SQLServer and
then for other applications.
Please read this to get some idea on how SQL Server memory
works. http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_config_9zfy.asp
Also , read "memory architecture" from BOL.
SQLVarad (MCDBA-1999,MCSE-1999)
>--Original Message--
>I have SQL 2000 server with two instances installed on
the
>box. The server has 4 GB RAM.
>(1)How can I choose SQL server memory settings to obtain
>better performance for both instances?
>(2)Should I choose "Dynamic" setting or "Fixed memory"
>setting?
>Thanks in advance.
>Don
>.
>|||Thanks for your good recommendation.
-Don
>--Original Message--
>Actually, there is no direct answer for this. It depends
> on how much memory is required for SQL Server and
>applications using this sql server.
>It also depends on any other applications running on the
> same box.
> In General, You have to allocate for OS,SQLServer and
>then for other applications.
> Please read this to get some idea on how SQL Server
memory
> works. http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/adminsql/ad_config_9zfy.asp
> Also , read "memory architecture" from BOL.
> SQLVarad (MCDBA-1999,MCSE-1999)
>>--Original Message--
>>I have SQL 2000 server with two instances installed on
>the
>>box. The server has 4 GB RAM.
>>(1)How can I choose SQL server memory settings to obtain
>>better performance for both instances?
>>(2)Should I choose "Dynamic" setting or "Fixed memory"
>>setting?
>>Thanks in advance.
>>Don
>>.
>.
>|||Don,
What edition of sql server are you running? If it's std edition then you
can only use 2GB (actually 1.7) per instance anyway. Unless you know for
sure that one instance always needs more than the other or you have other
apps that always require a specific amount you are probably best to leave it
dynamic. That way the two instances can give and take as needed.
--
Andrew J. Kelly
SQL Server MVP
"Don Walter" <dw_msg@.yahoo.com> wrote in message
news:078501c3a409$fa4199d0$a301280a@.phx.gbl...
> I have SQL 2000 server with two instances installed on the
> box. The server has 4 GB RAM.
> (1)How can I choose SQL server memory settings to obtain
> better performance for both instances?
> (2)Should I choose "Dynamic" setting or "Fixed memory"
> setting?
> Thanks in advance.
> Don