Problem
How do you set Max Server Memory?
Solution
The important factor here is that the buffer pool never acquires more memory than the level specified in max server memory
So how do you calculate it? This is a general calculation that can be used for determine the correct max server memory setting
MAX_SERVER_MEMORY= TOTAL_SERVER_MEMORY – [OS/Apps Memory] + [Threads * Thread Size]
OS/Apps Memory = 2 GB to 4 GB (I use 2GB on system with <= 16GB of RAM and 4GB on systems with > 16GB of RAM)
Threads = select max_workers_count from sys.dm_os_sys_info
/*Set the Max Server Memory*/
sp_configure ‘max server memory’, 27500
RECONFIGURE
GO
Tested Platform
Windows 2008 R2
MS SQL 2008 R2
Reference
SQL Server – Max Server Memory
Hits: 110