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

avatar image

J Angwenyi
115 7 9 12

(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

Edit: fixed broken link.

more ▼

answered Nov 05, 2010 at 07:44 AM

avatar image

4.9k 33 39 43

  • 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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(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

avatar image

12.1k 30 36 42

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Nov 05, 2010 at 07:37 AM

Seen: 1993 times

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

Copyright 2018 Redgate Software. Privacy Policy