x

SQL Server memory options

On SQL Server instance, Server memory options, we have the following settings:
Use AWE to allocate memory
Minimum server memory (in MB)
Maximum server memory (in MB)

On a typical server computer used to run other applications (not exclusively used as Database server), what will you recommend for the memory settings? Say it has 3GB RAM
more ▼

asked Nov 05, 2010 at 07:37 AM in Default

J Angwenyi gravatar image

J Angwenyi
115 6 6 9

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

4 answers: sort voted first

The AWE is useful if your machine is 32 bit so that SQL will be able to access more than 2GB of virtual address space. I haven't usually set the min server memory in my servers but you need to set the Max memory SQL can use so that your other applications and windows will not be memory starved since SQL will grab as much as it can. Personally I leave around 2GB or 10% of your memory which ever is bigger for windows and assign the rest to SQL Server. However if other apps are running on that machine(i.e. if it is not exclusively database server) you need to decide how much your app will need by monitoring for a while.

This awesome article explains in detail your options when configuring SQL Server memory. [SQL Server memory configuration][1]

Edit: fixed broken link.

[1]: http://www.eraofdata.com/blog/sql-server-memory-configuration/
more ▼

answered Nov 05, 2010 at 07:44 AM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

+1 for reference to the article
Nov 05, 2010 at 08:23 AM J Angwenyi
(comments are locked)
10|1200 characters needed characters left

That's an unanswerable question really.

The answer really is 'as much as possible'.

However, it really does depend. If you use SQL Server only very lightly, but you have a heavy weight application running on the server, then you don't want to give SQL Server too much.

If you are processing hundreds of queries, and your other application is MineSweeper, then you will want to give SQL Server quite a lot...

more ▼

answered Nov 05, 2010 at 07:40 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

I don't know - I hate when MineSweeper is sluggish :)
Nov 05, 2010 at 08:11 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

For a starting position I would want to leave 1GB for the OS (some are better than others and you may be OK with +- 30%) and have the remainder for SQL. If the server is running other apps too then SQL would have to have less. Start off with something you think is reasonable and then adjust according to which process needs the memory first. Then put in a PO for more RAM if its a busy/important box.

This is a good link for memory settings details http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx
more ▼

answered Nov 05, 2010 at 08:06 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

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

As others have stated - it depends, sorry.

Each implementation is unique and the settings will be dictated by the environment being run in.

As for the minimum setting... that is how low SQL Server will drop its memory to if under pressure from the O/S. Don't assume it is the minimum that SQL Server will start up with.

Some may slap me down for this but, you might just leave it at default and see what transpires. Consider it a baseline step.
more ▼

answered Nov 05, 2010 at 08:16 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

I wouldn't slap you down for the default setting suggestion. It does give you a good initial idea, after all.
Nov 05, 2010 at 08:23 AM Matt Whitfield ♦♦
(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:

x1945
x1834
x343

asked: Nov 05, 2010 at 07:37 AM

Seen: 1706 times

Last Updated: Nov 05, 2010 at 07:37 AM