what consideration should be there to set memory for sql server 2005? if i have 14 GB of memory, what parameter should be taken care to set Max memory for instance.
Please brief the solution.
asked
what should be the Max Memory for sql server 2005 if 16 GB Memory is avaiable?
I have two instance on a server with 20GB RAM and have set each instance to use no more than 9GB, therefore leaving 2GB for other applications and OS. The 9GB limit is often exceeded by each instance by a few MB but there is generally 1.5GB not used by SQL Server and this seems fine. I would not allocate <1GB for the OS, personally. This means that if everything else locks up, at least the OS will still be able to cope with me logging in to recover the server. In theory!
Definate + 1. Around 2gb for the OS is a decent rule of thumb. As with all things, don't take it for granted, test, test test on your own system.
I am uisng x64 server and 16GB of memory so that I don't need to turn the AWE (Address Windowing Extensions ) on and it will only need it in 32bit version of SQL Server. I don't change any value of my SQL server and I left as it is.
I used to have 32bit windows server and SQL 2008 32bit version, I have assigned to leaving 2GB left for Maximum Memory (for example ...16GB memory, Max Mem = 14GB).
Also Here is the explanation of Brad McGehee From RedGate....
If you are using the 32-bit version of SQL Server, and if you are using 4 GB or more of RAM, ensure that you have all the AWE settings correctly set.
In most cases, the settings for the “maximum server memory” and the “minimum server memory” should be left to their default values. This is because the default values allow SQL Server to dynamically allocate memory in the server for the best overall optimum performance. Exceptions to this rule of thumb can come into play if you use 32-bit AWE memory, or 64-bit memory, where you may have to change the “maximum server memory” to an amount that is less than the physical amount of RAM in your server.
I hope it helps.
