DB-유

Configuring SQL Server Max Server Memory Correctly

· mssql, performance, memory

By default max server memory is set to 2147483647 MB, which effectively means "take everything". SQL Server will happily grow its buffer pool until Windows starts paging, and then you get the classic symptoms: slow RDP sessions, a monitoring agent that stops responding, backup jobs that time out, and a server that feels frozen even though SQL Server itself reports no problem. I've lost enough time RDP'ing into boxes like that that setting a cap is now the first thing I do on every new instance. No restart needed.

Where are you now?

SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'min server memory (MB)');

SELECT physical_memory_kb / 1024 AS server_memory_mb
FROM sys.dm_os_sys_info;

SELECT physical_memory_in_use_kb / 1024 AS sql_in_use_mb,
       locked_page_allocations_kb / 1024 AS locked_pages_mb,
       memory_utilization_percentage
FROM sys.dm_os_process_memory;

If value_in_use for max server memory is 2147483647, you are running on defaults.

Picking a number

The number in max server memory covers the buffer pool plus most other memory clerks (plan cache, CLR, most of the query workspace memory, since SQL Server 2012). It does not cover thread stacks, the SQL Server executable, linked server providers, extended stored procedures, or anything else on the box such as SSIS, SSRS, an antivirus agent or a backup agent.

A conservative formula that has served me well:

  • Reserve 1 GB for the operating system
  • Reserve another 1 GB for every 4 GB of RAM up to 16 GB
  • Reserve another 1 GB for every 8 GB above 16 GB
  • Then subtract whatever else runs on the server (SSIS packages, SSRS, other instances)

For a dedicated 32 GB server that gives roughly 1 + 4 + 2 = 7 GB reserved, so max server memory around 25 GB, or 25600 MB. For a 64 GB server it lands around 54 GB. Some people leave less, and if Available MBytes in Performance Monitor stays comfortably above 1–2 GB under load you can tighten it later.

If you run two instances on the same host, split the memory between them explicitly; two instances with default settings will fight each other.

Setting it

The change takes effect immediately:

EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'max server memory (MB)', 25600;
RECONFIGURE;

If you prefer the GUI: in SSMS right-click the instance, Properties → Memory → Maximum server memory (in MB).

Lowering the value on a busy server causes SQL Server to shrink the buffer pool right away, so expect a short burst of physical reads afterwards. I do it in a quiet window out of habit, but it is not dangerous.

min server memory and Lock Pages in Memory

min server memory sets a floor below which SQL Server will not release memory once it has reached it. On a dedicated box I leave it at 0 or set it to half of max; it mostly matters when other processes compete for RAM.

Lock Pages in Memory is a Windows user right that lets the SQL Server service account keep buffer pool pages from being paged out. On Standard and Enterprise Edition (2008 R2 SP1 and later) it works without a trace flag. Grant it through secpol.msc → Local Policies → User Rights Assignment → Lock pages in memory, add the service account, and restart SQL Server. Only do this together with a sensible max server memory, otherwise you are just making the paging problem worse for everyone else.

Did it work?

Confirm the value and that the buffer pool respects it:

SELECT name, value_in_use FROM sys.configurations WHERE name = 'max server memory (MB)';

SELECT type, SUM(pages_kb) / 1024 AS mb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY mb DESC;

The MEMORYCLERK_SQLBUFFERPOOL clerk should be the biggest line and the total should stay under your cap. In the SQL Server error log you will also find a line like Using locked pages in the memory manager if Lock Pages in Memory is active, or Using conventional memory in the memory manager if it is not.

Watch these Performance Monitor counters for a day:

  • Memory\Available MBytes — should stay well above 1 GB
  • SQLServer:Buffer Manager\Page life expectancy — a sudden permanent drop after lowering the cap means you took too much away
  • SQLServer:Memory Manager\Target Server Memory (KB) and Total Server Memory (KB) — Total should approach Target and then stay flat

Gotchas

  • A significant part of sql server process memory has been paged out. This may result in a performance degradation. This message in the error log is the exact symptom the cap prevents. Set max server memory and consider Lock Pages in Memory.
  • Msg 5845: Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege. Only relevant on 32-bit; ignore on x64.
  • Set the value too low by mistake (e.g. 256 instead of 25600). SQL Server may become unresponsive. Connect with the Dedicated Administrator Connection (sqlcmd -S admin:servername -E) and run sp_configure again. This is the main reason to keep the DAC enabled.
  • Reporting Services or SSIS on the same box still starve. Remember that those are separate processes with their own memory; lower max server memory further or move them.

There is no universally right number, but almost any explicit number beats the default. I adjust in 1-2 GB steps and don't touch it again until the workload changes.