x

Memory usage - SQL Server 2005 Standard

Hello,

We are running SQL Server 2005 standard on a Windows Server 2003 R2 Standard box with 4GB RAM.

Am I correct in thinking that increasing our memory will give no performance improvements at all?

Regards,

Graham
more ▼

asked Oct 07, 2011 at 08:17 AM in Default

grazer gravatar image

grazer
1 1 1 1

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

2 answers: sort voted first

No, you can enable the server to use more than 4 GB of ram. I have several Windows 2003 box with 16 GB of ram and SQL performs much better when it has the right amount of memory.

Once you add more memory, enabled AWE on your SQL Server, the the max memory to the right size (leave enough for the OS), and enable the 3GB and PAE in your boot.ini file.
more ▼

answered Oct 07, 2011 at 08:19 AM

Tim gravatar image

Tim
36.4k 38 41 139

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

"It depends". Principally on your OS - are you running on 64-bit, or 32-bit? See http://msdn.microsoft.com/en-us/library/windows/desktop/aa366778(v=vs.85).aspx#physical_memory_limits_windows_server_2003_r2 for the memory limits.

SQL Server itself is limited to the memory that the OS can support - no shock there.

If you do boost the memory, it may be worth investigating the "Lock pages in memory" permission for the SQL server service - see http://sqlserverperformance.wordpress.com/2011/02/14/sql-server-and-the-lock-pages-in-memory-right-in-windows-server/ for more details. But, if you do do this, remember to set a maximum memory size of, say, 3GB less than the system total (more if you are running other apps on the server), to give the OS room to manouevre.
more ▼

answered Oct 09, 2011 at 01:39 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

(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:

x1949
x729
x58

asked: Oct 07, 2011 at 08:17 AM

Seen: 1127 times

Last Updated: Oct 07, 2011 at 08:17 AM