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?
asked Oct 07, 2011 at 08:17 AM in Default
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.
"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.