Memory usage - SQL Server 2005 Standard


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?



more ▼

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

avatar image

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

avatar image

40.9k 39 95 168

(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

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

(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: Oct 07, 2011 at 08:17 AM

Seen: 1305 times

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

Copyright 2018 Redgate Software. Privacy Policy