Sakthivel Chidambaram recently created a calculator which can find out the max server memory value based on the input.
http://blogs.msdn.com/b/sqlsakthi/archive/2012/05/19/cool-now-we-have-a-calculator-for-finding-out-a-max-server-memory-value.aspx
you can try the tool from http://blogs.msdn.com/b/sqlsakthi/p/max-server-memory-calculator.aspx
Then Jonathan Kehayias made a post to clarify why the calculator doesn't work
http://www.sqlskills.com/blogs/jonathan/2012/05/default.aspx
Jonathan has another great post relative the max server memory
http://www.sqlskills.com/blogs/jonathan/post/How-much-memory-does-my-SQL-Server-actually-need.aspx
This reminded me another max server memory formula which I follow up for many years posted by other MVP Glenn Berry
a little script which I used to run from central management server and configured the max server memory for all sql servers
====================================================================
DECLARE @curMem int
DECLARE @maxMem int
DECLARE @sql varchar(max)
select @curMem=physical_memory_in_bytes/1024/1024 from sys.dm_os_sys_info
SET @maxMem = CASE
WHEN @curMem < = 1024*2 THEN 1500
WHEN @curMem < = 1024*4 THEN 3200
WHEN @curMem < = 1024*6 THEN 4800
WHEN @curMem < = 1024*8 THEN 6400
WHEN @curMem < = 1024*12 THEN 10000
WHEN @curMem < = 1024*16 THEN 13500
WHEN @curMem < = 1024*24 THEN 21500
WHEN @curMem < = 1024*32 THEN 29000
WHEN @curMem < = 1024*64 THEN 60000
WHEN @curMem < = 1024*72 THEN 68000
WHEN @curMem < = 1024*96 THEN 92000
WHEN @curMem < = 1024*128 THEN 124000
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)
====================================================================
However, with Jonathan's post, that formula might not correct, and especially have on the big memory systems. There are 2 options which are mentioned in Jonathan's post
1. "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. This has typically worked out well for servers that are dedicated to SQL Server. "
2. "((Total system memory) – (memory for thread stack) – (OS memory requirements ~ 2-4GB) – (memory for other applications) - (memory for multipage allocations; SQLCLR, linked servers, etc)), where the memory for thread stack = ((max worker threads) *(stack size)) and the stack size is 512KB for x86 systems, 2MB for x64 systems and 4MB for IA64 systems. The value for 'max worker threads' can be found in the max_worker_count column of sys.dm_os_sys_info "
I think I will use the first option as an initial setup, then follow up Jonathan's post to monitor the system memory status, and adjust it as needed. here is the script for the option 1
--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)
select @curMem=physical_memory_in_bytes/1024/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)
except the max server memory, here are some other post regarding the sql memory setting:
1. Fun with Locked Pages, AWE, Task Manager, and the Working Set…
http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx
2. Be Aware: Using AWE, locked pages in memory, on 64 bit
http://blogs.msdn.com/b/slavao/archive/2005/04/29/413425.aspx
3. Q & A: Does SQL Server always respond to memory pressure?
http://blogs.msdn.com/b/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx
4. Importance of setting Max Server Memory in SQL Server and How to Set it
http://blogs.msdn.com/b/sqlsakthi/archive/2011/03/12/importance-of-setting-max-server-memory-in-sql-server-and-how-to-set-it.aspx
