June 6, 2006 at 6:40 pm
Hi Guys,
We are going to install the SQL server enterprise edition on Win 2003 advanced server with 4GB RAM.
How should we divide the memory between the SQL server and operating system? and where do we do it? This server is going to handle nearly 200GB database.
Regards,
TK
June 6, 2006 at 7:05 pm
You are pretty much limited with only 4GB RAM. Assuming the server to be primarily a database server, then the maximum RAM that SQL Server can use is 3 GB, and that is with the '/3GB' switch enabled in W3K and 'AWE' enabled on SQL 2K.
Depending on whether the database will be mostly read, write or somewhere in-between will determine if more memory is required, so that both data and procedure cache sizes are optimal for your particular database server.
Of course we shouldn't simply look at memory in isolation, but need to consider as well the number of CPUs, RAID/SAN configuration, etc.
June 6, 2006 at 7:22 pm
This server has a QUAD 3.6 GHz processor with 6 drives (probably a setup of RAID 1 and 0) and yes, the data will be stored to SAN as well.
I hope I answered the questions, please let me know if I havn't.
June 6, 2006 at 7:40 pm
And yes, it will be clustered as well.
June 8, 2006 at 7:02 am
If it's just doing SQL Server work then give it the /3GB switch in boot.ini to let SQL use 3GB of RAM leaving 1GB for the OS. If you have other processes on the box then you'll have to weigh it up.
June 8, 2006 at 4:35 pm
Don't forget the following configuration options as well:
exec sp_configure 'min server memory (MB)', 3072
exec sp_configure 'max server memory (MB)', 3072
exec sp_configure 'awe enabled' , 1
exec sp_configure 'set working set size', 1
go
reconfigure with override
go
--
-- reboot each side of the cluster after the configurations aer set
--
Also, you can use more than 3 Gb of RAM. You'll need the /PAE flag in the boot.ini file instead of the /3GB. However, PAE uses 1 GB of RAM itself to map additional RAM. It's suggested that you use this option only on servers with lots (12-16+ Gb) of RAM.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 9, 2006 at 11:54 am
Well I am working with the same issue these days. We had a 4GB server however now its 8GB and enabling AWE.
I wont recommend /3GB switch in this case only because according to the authors of SQL Server 2000 unleashed by SAMS, one should leave about 50% of memory for the OS, stored procedures execution etc. By allowing SQL Server to access 3 GB and a big database you might endup cutting corners on performance.
I would suggest to increase memory to 8 GB.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply