x

what should be the Max Memory for sql server 2005 if 16 GB Memory is avaiable?

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.

more ▼

asked Feb 01 '10 at 08:12 AM in Default

pxt_ce gravatar image

pxt_ce
27 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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!

more ▼

answered Feb 01 '10 at 08:19 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

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.
Feb 01 '10 at 10:14 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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....

  1. 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.

  2. 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.

more ▼

answered Feb 01 '10 at 02:26 PM

Leo gravatar image

Leo
1.6k 51 56 58

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x53

asked: Feb 01 '10 at 08:12 AM

Seen: 1916 times

Last Updated: Feb 01 '10 at 08:12 AM