SQL Server Interview Question #277

What is max server memory?

SQL Server Internals & Administration Senior Advanced

Detailed Explanation

max server memory limits how much memory SQL Server's buffer pool and certain associated memory allocations can consume under the setting's scope.

It should normally be configured so the operating system and other processes have sufficient memory. Setting it equal to total physical RAM can create OS memory pressure.

The correct value depends on total RAM, SQL Server features, workload, other services on the host, and deployment architecture.

Code Example

EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sys.sp_configure 'max server memory (MB)';