DBAtlas · Database operations atlas

Configure with sp_configure (max server memory)

SQL ServerConfiguration

Command

-- Make advanced options visible:
EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Set max server memory (MB). Example: allow 24 GB:
EXEC sys.sp_configure 'max server memory (MB)', 24576;
RECONFIGURE;

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

What it does

Caps the maximum memory SQL Server can use for the buffer pool; leaves RAM for the operating system and other components.

When to use it

When the OS/other services on the server are starved of memory, or new RAM has been added.

Caution

Leave enough RAM for the operating system, SSAS/SSIS, CLR and thread stacks (usually at least 4-6 GB for the OS). Setting it too low cripples SQL Server performance. The change is not applied without RECONFIGURE; this setting is dynamic (no restart required).

Open in DBAtlas →

Related