Quick Scan Report – Max Server Memory

What this check looks for

sys.configurations where max server memory (MB) is still 2147483647, the installed default. The finding also reports how much physical memory the machine has, read from xp_msver, so the number you need is in front of you.

2147483647 is 2 TB expressed in megabytes. It is not a real limit; it is the way SQL Server spells “no limit”.

Why it matters

SQL Server is designed to use all the memory it is allowed to, and it does not give it back gracefully.

The buffer pool grows as pages are read, and the engine has no reason to stop while memory remains. On a machine where nothing has told it otherwise, it will keep taking until Windows starts signalling memory pressure. Then:

• Windows begins trimming working sets, including SQL Server’s, which means pages the engine expected in memory are on disk again.
• The operating system itself is short. The file system cache, drivers and the network stack are all competing with a process that has already taken what they wanted.
• Everything else on the box suffers, which on a shared server means the antivirus agent, the backup agent, the monitoring agent and any other SQL Server instance.
• Recovering is slow. SQL Server responds to memory pressure by shrinking the buffer pool, which means dropping cached pages, which means reading them from disk again. The instance gets slower at the moment it is already under pressure.

The visible symptom is unhelpful: the server looks fine and feels slow. CPU is not pegged, there is no blocking, and the wait statistics show I/O because the cached pages that should be in memory are being read back.

On a virtual machine it is worse, because the host may be over-committed and ballooning, and a guest with no memory limit fights the hypervisor as well as its own operating system.

How to confirm it yourself

SELECT [name], [value], [value_in_use], [is_dynamic], [description]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] IN ('max server memory (MB)', 'min server memory (MB)');

How much memory the machine has and how much SQL Server is using:

SELECT [total_physical_memory_kb] / 1024 / 1024 AS [server_ram_gb],
       [available_physical_memory_kb] / 1024 / 1024 AS [available_gb],
       [system_memory_state_desc]
  FROM sys.dm_os_sys_memory WITH (NOLOCK);

SELECT [physical_memory_in_use_kb] / 1024 / 1024 AS [sql_using_gb],
       [large_page_allocations_kb] / 1024 AS [large_pages_mb],
       [page_fault_count],
       [memory_utilization_percentage],
       [process_physical_memory_low],
       [process_virtual_memory_low]
  FROM sys.dm_os_process_memory WITH (NOLOCK);

system_memory_state_desc is the one to read. Anything other than “Available physical memory is high” means the machine is already under pressure.

And whether it is hurting, from the wait statistics:

SELECT [wait_type], [waiting_tasks_count], [wait_time_ms] / 1000 AS [wait_seconds]
  FROM sys.dm_os_wait_stats WITH (NOLOCK)
 WHERE [wait_type] IN ('RESOURCE_SEMAPHORE', 'PAGEIOLATCH_SH', 'PAGEIOLATCH_EX',
                       'MEMORY_ALLOCATION_EXT', 'CMEMTHREAD')
 ORDER BY [wait_time_ms] DESC;

How to fix it

Set it. The change is dynamic and takes effect immediately, with no restart:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 24576;   -- 24 GB, for a 32 GB machine
RECONFIGURE;

How much to leave the operating system, as a starting point on a dedicated SQL Server:

Subtract more than that if anything else runs on the machine: another SQL Server instance, SSIS, SSRS, SSAS, or an application. Each named instance needs its own limit, and the sum of all of them plus the operating system’s share has to fit.

Then watch it. Lowering it too far is its own problem, and the signal is page life expectancy falling and PAGEIOLATCH waits rising. Give it a day under real load and compare.

Two related settings worth a glance while you are here:

• min server memory is worth setting on a shared machine, so the instance keeps a floor when something else grabs memory. Leave it at 0 on a dedicated one.
• Lock Pages in Memory, a Windows privilege for the service account, stops Windows trimming SQL Server’s working set. Useful on a machine that has been observed paging, and it should be a considered choice rather than a default.

How long it takes

About an hour, including watching the effect under load. The change itself is one statement.

Related reports

Related checks

Frequently asked questions

It is a dedicated SQL Server, so why not let it have everything? Because Windows still needs memory, and SQL Server does not leave it any by choice. The limit is how you reserve the operating system’s share.

Does lowering it require a restart? No. It is dynamic. SQL Server releases memory over the following minutes.

We have 512 GB. Is the table above still right? The proportion shrinks on very large machines; leaving 16 to 32 GB is usually sufficient. The principle is to reserve enough for the operating system and anything else running, not a fixed percentage.

What about min server memory? Set it on a shared machine so the instance keeps a floor. On a dedicated one, 0 is fine, and setting both to the same value is a way of fixing the buffer pool size deliberately.