Configuration of memory for SQL 2000

[mipo]

I am looking for recommandations regarding the memory distribution for SQL 2000 server. In the memory tab of the server properties you can define use a fixed memory size or dynamically configure SQL server or even reserve physical memory for SQL server.
Are there any hints available or must it be handled according trial and error?

Thanks for any info

mipo

[skhanal]

If you are running only sql server in this box then use dynamic option. To make sure this is the right option for you monitor the pages/sec counter in system monitor, if you see this counter consistently higher than 20, use fixed memory (allocate 90% to sql server).

If you have more than 4GB memory then you have to use fixed server memory.

[flyingbuick]

Originally posted by skhanal
If you are running only sql server in this box then use dynamic option. To make sure this is the right option for you monitor the pages/sec counter in system monitor, if you see this counter consistently higher than 20, use fixed memory (allocate 90% to sql server).

If you have more than 4GB memory then you have to use fixed server memory.

90% for SQL Server? That seems really high to me. I doubt you can but even if you're actually successful in doing that, the OS will be probably be paging like crazy.

For a dedicated server (ie, only runs SQL Server on the machine and nothing else) leaving it at dynamic configuration works best. SQL Server will request as much memory as it needs from the OS and this continues until the OS cannot give out more memory without negatively impacting the system. SQL Server then holds on to the memory until the OS asks for it back. I really see no need for manual configuration if it's a dedicated server.

Agree with you on the large memory boxes though.