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 poolfilesystem cachePTE, 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?



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?



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:



x86 - AWE


Set Max. Server Memory



Not Needed

sp_configure 'awe enabled'

Not Needed


Not Needed

Lock Pages in Memory

(To use AWE API, Large Page Support)


Not Needed

What components within SQL Server use Memory from BPool?

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

More info:

Further Reads:
Server Memory Options -
awe enabled Option -
Enabling Memory Support for Over 4 GB of Physical Memory -
Using AWE and limitation of Memory which SQL Server 32-bit instance can access using AWE -
Lock Pages in Memory ... do you really need it? -
Windows: Physical Memory -

Sakthivel Chidambaram, SQL Server Support

Comments (11)
  1. noel dsouza says:

    thanks Shakti…:) was searching for this vkb would have not:)

  2. Samir Pathak says:

    I have SQL server 208 R2 installed on machine. Machine has 16 GB of RAM. SQL consumes around 14 GB of RAM causing other application on that machine to run slow. Is it because of log file of SQL? Whats the solution.

  3. Hi Samir,

    Check your "Available MBytes" counter to see whether your system is actually encountering Memory pressure.

    "Other application run slow" could be because of many things.

  4. Anonymous says:

    For active active setup, if you consider doubling the RAM in order to consider failover situation, it is a poor design buddy. Wasting half  of the memory  at any given point of time is a stupid idea. I would suggest to implement some automation such as WMI script or startup script etc…

    Response from Sakthi:

    Thanks for the feedback. What if one node out of Active-Active cluster crashed and it takes 1 day to rebuild the system? Are you planning to run two mission critical instances in 1 node with same amount of RAM which is sufficient to handle one instance? Even in Active/Passive scenario, you always have a passive node which is idle with equivalent RAM.. Do you think A/P clustering itself is a stupid concept?

  5. Dinesh Vishe says:


  6. Steve says:

    Silly question, how to run "sp_configure 'max server memory (MB)',<Memory in MB>" what tool do I use to do this?  DOS command line doesn't recognize it as a legitimate program or command…

  7. It's a T-SQL command Steve. You need to run this in SSMS or SQLCMD connected to the target SQL Server instance.

  8. diogenes says:

    this is a joke right? I mean the simple formula comment: you couldn't even lfinish listing the elements here 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…)   let alone the formula or location for calculating each element, and then, you have to do the same process for each component. REally. Playing keep away with manageable facts to run a manageable system, unless of course you are a giant bureaucracy and all you have to to all day is look at these minutae: but that is the 2012 / 2013 generation of Microsoft "built by bureaucracies for bureaucracies"

  9. ashish says:

    IF OBJECT_ID('tempdb..#mem') IS NOT NULL DROP TABLE #mem



    @memInMachine DECIMAL(9,2)

    ,@memOsBase DECIMAL(9,2)

    ,@memOs4_16GB DECIMAL(9,2)

    ,@memOsOver_16GB DECIMAL(9,2)

    ,@memOsTot DECIMAL(9,2)

    ,@memForSql DECIMAL(9,2)

    ,@CurrentMem DECIMAL(9,2)

    ,@sql VARCHAR(1000)

    CREATE TABLE #mem(mem DECIMAL(9,2))

    –Get current mem setting———————————————————————————————-

    SET @CurrentMem = (SELECT CAST(value AS INT)/1024. FROM sys.configurations WHERE name = 'max server memory (MB)')

    –Get memory in machine————————————————————————————————


     SET @sql = 'SELECT physical_memory_in_bytes/(1024*1024*1024.) FROM sys.dm_os_sys_info'


      IF CAST(LEFT(CAST(SERVERPROPERTY('ResourceVersion') AS VARCHAR(20)), 2) AS INT) >= 11

        SET @sql = 'SELECT physical_memory_kb/(1024*1024.) FROM sys.dm_os_sys_info'


        SET @sql = 'SELECT physical_memory_in_bytes/(1024*1024*1024.) FROM sys.dm_os_sys_info'

    SET @sql = 'DECLARE @mem decimal(9,2) SET @mem = (' + @sql + ') INSERT INTO #mem(mem) VALUES(@mem)'

    PRINT @sql


    SET @memInMachine = (SELECT MAX(mem) FROM #mem)

    –Calculate recommended memory setting———————————————————————————

    SET @memOsBase = 1

    SET @memOs4_16GB =


       WHEN @memInMachine <= 4 THEN 0

      WHEN @memInMachine > 4 AND @memInMachine <= 16 THEN (@memInMachine – 4) / 4

       WHEN @memInMachine >= 16 THEN 3


    SET @memOsOver_16GB =


       WHEN @memInMachine <= 16 THEN 0

      ELSE (@memInMachine – 16) / 8


    SET @memOsTot = @memOsBase + @memOs4_16GB + @memOsOver_16GB

    SET @memForSql = @memInMachine – @memOsTot

    –Output findings——————————————————————————————————


    @CurrentMem AS CurrentMemConfig

    , @memInMachine AS MemInMachine

    , @memOsTot AS MemForOS

    , @memForSql AS memForSql

    ,'EXEC sp_configure ''max server memory'', ' + CAST(CAST(@memForSql * 1024 AS INT) AS VARCHAR(10)) + ' RECONFIGURE' AS CommandToExecute

    ,'Assumes dedicated instance. Only use the value after you verify it is reasonable.' AS Comment

  10. Ravi says:

    Hi Chidambaram,

    I have a Active-Passive SQL Cluster and need to change the Max memory.

    So, I change the max memory in Passive server first and failover the services and change the max memory in active server.

    IS the above procedure correct?

    Thanks in advance

    ** Reply from Sakthi

    A/P Cluster means it is a single instance of SQL Server and any configuration change you make will persist even after failover so you don't need to make this change again.

  11. Eric Fung says:

    Hi Sakthivel,

    Per your prepared table, in 32 bit x86 (without AWE) OS, there is no need to specify Max Memory in SQL Server (I understand as there are already 2GB / 3GB limit from OS imposed.)

    However, would there still be any advantage, if MAX memory setting is still done in 32 bit OS ?

    Imaginary Scenario :

    When SQL server already suffer from critical high memory usage

    (very close to 2GB max used) i.e. different running SQL engine thread all attempt to acquire more memory to complete its own processing.

    For system having MAX memory set, then SQL Server could check for if extra attempt to acquire memory would then resulted in MAX memory setting exceeded, thus it can proceed to corr. handling for no more memory available (e.g. reject the latest request due to no more memory available)

    For System having no MAX memroy set (i.e. SQL server assume there is unlimited memory), then SQL Server may not beware such acquire cannot never be entertain, due to OS limit, thus may instead falls into end-less retry, hoping next acquire would then being granted from OS by then …

    Reason for query :

    We recently encounter a few times an old SQL server suffer from not responsive situation. Even though we have DAC session enble, we cannot use it to diagnosis what happens by that time. At the end we need to restart the SQL server service.

    During post event review, we found from SQL Server error log, that such not responsive event also preceded some time before by message "… marked for unload due to memory pressure". So it leads to think of the even due to memory issue.

    An comment would be much appreciated.



Comments are closed.

Skip to main content