Importance of setting Max Server Memory in SQL Server and How to Set it

UPDATE: This blog is applicable for SQL Server versions till SQL 2008 R2. Memory Manager has changed in SQL 2012 and above

Of late, I'm observing that some of the customers are not setting up Max. Server Memory Properly or they never set Max. Server Memory at all in 64-bit SQL Server installations.

What will happen if I don't set Max. Server Memory?

Working Set trimming, Operating System unresponsiveness, Performance Problems in other applications running on the same server, Downgraded Backup Buffers etc...

How should I set Max. Server Memory?

sp_configure 'max server memory (MB)',<Memory in MB>

Is Max. Server Memory the upper limit to which SQL Server will consume Memory in the box?

No. Max. Server Memory just limits the memory consumed by SQL Server Buffer Pool. Memory consumed by Non-buffer pool portion of SQL Server and memory consumed by external dll's loaded into SQL Server memory space is not controlled using Max. Server Memory

How can I calculate the value for Max. Server Memory?

In general, you should calculate Max. Server Memory using the formula:

Max. Server Memory for a SQL Server Instance =

(Total RAM available to the OS) -

{

(Memory needed by Operating System which gets allocated to memory pool ,  filesystem cache ,  PTE , desktop heap , Driver Images etc...) +

(Memory needed by Non-buffer Pool region of SQL Server which gets allocated to Multi Page Allocators, Worker Threads, COM, Extended SPs, Backup Buffers, CLR, Linked Server...) +

Memory required for SQL Server Agent, Replication Agents, Bulk Copy, SSRS, SSAS, SSIS, and Full Text +

Memory required for Log shipping file copy depending on the size of log backups (if LS is configured) +

Memory required for other SQL Server instances running in the box +

Memory required for other applications running in the box (Antivirus, Monitoring Softwares, Compression softwares etc...)

}

I'm sure this is not a tough formula. Please note that stack size of SQL Server in x64 is 2 MB so depending on the amount of worker threads calculated by SQL Server (SELECT max_workers_count FROMsys.dm_os_sys_info) you may need to deduct memory in the above formula. Also if you have enabled -g startup parameter then you need to deduct the memory in the above formula accordingly. Make sure that you collect these memory requirements during the peak load.

I'm giving some scenarios explaining how to set Max. Server Memory here:

Scenario #1:

I have Active-Active SQL Server instances running on Node A and Node B. I have 65 GB of RAM in each Node A and Node B. I have set Max. Server Memory for both the instances InstA and InstB to 60 GB leaving 5 GB to operating systems. Is it a right configuration?

NO.

Why?

Imagine a situation where both the instances are running on same node due to some issues on the other node. So if both the instances are memory hungry then you will have the worst performance possible. So for a perfect configuration, you should have 125 GB RAM on each node so that at any point, there will be no memory bottleneck immaterial on which node both the instances are running. Atleast you should have physical memory more than sum of Min. Server Memory of both the instances.

Scenario #2:

I have 3 SQL Server instances running on box SQLSRVR. I have 65 GB of RAM in SQLSRVR. I have set Max. Server Memory on each of the three instances to 60 GB leaving 5 GB to operating systems. Is it a right configuration?

NO.

Why?

This setting means that all the three instances can grab upto 60 GB of memory and if that happens then all the three instances will be performing poorly. Refer to the formula above and set Max. Server Memory accordingly to all the three instances. If that is something not possible, atleast you should have physical memory on the box which is more than sum of Min. Server Memory of all the instances.

Here is snippet out of a doc I prepared for a customer:

Parameter

x64

x86 - AWE

x86

Set Max. Server Memory

Must

Must

Not Needed

sp_configure 'awe enabled'

Not Needed

Needed

Not Needed

Lock Pages in Memory

Optional (To use AWE API, Large Page Support)

Needed

Not Needed

What components within SQL Server use Memory from BPool?

Database Page Cache
Log caches
Procedure/Plan cache
Query Workspace
Locks
Connection context
Optimizing queries
System-level data structures etc.,

More info:
https://blogs.msdn.com/b/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx
https://support.microsoft.com/kb/918483

Further Reads:
Server Memory Options -  https://msdn.microsoft.com/en-us/library/ms178067.aspx
awe enabled Option - https://msdn.microsoft.com/en-us/library/ms190731.aspx
Enabling Memory Support for Over 4 GB of Physical Memory - https://msdn.microsoft.com/en-us/library/ms179301.aspx
Using AWE and limitation of Memory which SQL Server 32-bit instance can access using AWE - https://msdn.microsoft.com/en-us/library/ms175581.aspx
Lock Pages in Memory ... do you really need it? - https://blogs.technet.com/b/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx
Windows: Physical Memory - https://blogs.technet.com/b/markrussinovich/archive/2008/07/21/3092070.aspx

Sakthivel Chidambaram, SQL Server Support