SQL Memory configuration

Memory is cheap and often easy to expand in a modern Windows server. But do not forget that OS needs memory to handle the memory for all processes. Therefore you should never use default memory settings. It is Best Practice to configure SQL to not use all the memory in a server.

Good guidelines for memory allocation:
-Reserve 1 GB RAM for OS
-Reserve additional 1 GB to  OS for every 4 GB of RAM installed on the server between 4-16 GB
-Reserve additional 1 GB to OS for each 8 GB of RAM installed on the server over 16 GB of RAM.
Sounds complicated?
Below is a script that does exactly this for you.
If you have a large environment you can use Central Management Server to run this script on many servers simultaneously and in a quick and easy way ensure that your SQL Server has a good memory configuration. This configuration works for most of the SQL Server installations but there are of course servers that needs different memory configuration. When you run this command (sp_configure) it will flush the cache. Some users may think the system is a bit slow for a while until the cache is rebuilt.

If you have SSRS or SSIS or other services that require memory, you need to take into consideration this when assigning memory to SQL services.

(Note that there are two different lines depending on which version of SQL to run the script against)

--Automatically configure SQL Memory Setting according to MS Best Practice

--reserve 1 GB of RAM for the OS,

--1 GB for each 4 GB of RAM installed from 4–16 GB,

--and then 1 GB for every 8 GB RAM installed above 16 GB RAM

DECLARE @curMem int

DECLARE @maxMem int

DECLARE @sql varchar(max)

---Uncomment the line below according to your version

---- SQL 2008
--SELECT @curMem=physical_memory_in_bytes/1024/1024 from sys.dm_os_sys_info

---- SQL 2012 & 2014
--SELECT @curMem=physical_memory_kb/1024 from sys.dm_os_sys_info

SET @maxMem = CASE

WHEN @curMem < = 1024*2 THEN @curMem - 512

WHEN @curMem < = 1024*4 THEN @curMem - 1024

WHEN @curMem < = 1024*16 THEN @curMem - 1024 - Ceiling((@curMem-4096) / (4.0*1024))*1024

WHEN @curMem > 1024*16 THEN @curMem - 4096 - Ceiling((@curMem-1024*16) / (8.0*1024))*1024

END

SET @sql='

EXEC sp_configure ''Show Advanced Options'',1;

RECONFIGURE WITH OVERRIDE;

EXEC sp_configure ''max server memory'','+CONVERT(VARCHAR(6), @maxMem)+';

RECONFIGURE WITH OVERRIDE;'

EXEC(@sql)

This script and some explanations is found here:

http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/06/02/max-server-memory/