SQL Server – Max Server Memory

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

Leave a Reply