Karthick P.K on SQL Server

Max server memory – Do I need to configure?

Posted by Karthick P.K on April 22, 2013

Do I need to configure Max server memory and min server memory?  What is the right value for  this configuration and how to determine it?

There are many debates around this and above questions are raised by many SQL Server DBA’s frequently in many forums. If you ask me , “It depends” on various factors.

Before we choose to configure or leave this value to default it is very important to understand how SQL Server grow and shrink its memory usage based on the available memory in operating system even when Max server memory is not configured or defaulted.

How SQL Server grow and shrink its memory usage based on the available memory in operating system even when Maximum server memory is not configured or defaulted?

 SQL Server memory management is designed to dynamically adjust its memory usage based on the amount of available memory on the system. SQL Server will keep allocating memory based on its need as long as there is memory available I.e. as long as MEMPHYSICAL_HIGH (HighMemoryResourceNotification )notification is signaled in widows  and will scale down its usage when there is MEMPHYSICAL_LOW (LowMemoryResourceNotification) signaled in windows. When available memory is between the low memory and high memory SQL Server will try to maintain the memory usage stable( RESOURCE_MEM_STEADY) with some exceptions.

You can download the ResourceNotificationHighandLow.exe from This link to see memory notifications from windows.

The default level of available memory that signals a LowMemoryResourceNotification event is approximately 32 MB per 4 GB, to a maximum of 64 MB. (By default, the threshold is 64mb on most systems).

The default level that signals a high-memory-resource notification event is three times the default low-memory value (By default, the threshold is 64*3=192 MB on most systems).

Key points:

1. Once the available memory on the system goes below 192 MB HighMemoryResourceNotification (MEMPHYSICAL_HIGH) signal is revoked by windows and SQL Server will not grow its Bpool.

2. Once the available memory on the system goes below 64 MB LowMemoryResourceNotification (MEMPHYSICAL_low) is signaled by windows and SQL Server will shrink its Bpool (reduce its memory usage).

3. When the available memory in the system is between 192Mb and 64 Mb (I.e between LowMemoryThreshold and HighMemoryThreshold) SQL Server will not grow or shrink its usage (With some exceptions which we will see in a while)

Note: So unless there is an crazy application in the system that keeps allocating and releasing memory in Zigzag fashion making  windows trigger HighMemoryResourceNotification and LowMemoryResourceNotification one after the other SQL Server will not grow and shrink its memory usage in Loop continuously. If there are such application in system then even configuring max server memory may not help.

The default Low memory threshold 64MB may not be ideal for all systems.  Ex: Let as assume an application is requesting 150MB of memory suddenly when the available memory is 190 MB and the grant is successful. Available memory will now drop to 40 MB making windows signal the LowMemoryResourceNotification. SQL Server will start responding to the LowMemoryResourceNotification from windows but at the same time windows working set manager will also start trimming the working set of all the processes. Which will bring down the overall performance of the system.

We can increase the LowMemoryThreshold value by making the following registry changes If LowMemoryThreshold set to higher value  OS will notify applications such as SQL on low memory conditions much earlier and SQL Server can respond to memory pressure much early before the system starves for memory and before windows working set manger starts trimming the working set of all the processes.

In Regedit -> go to


Right click on the right pane,

Select New -> select click DWORD Value -> enter LowMemoryThreshold

Double Click LowMemoryThreshold -> value (choose decimal) -> 512

System Reboot is required to take effect.

In the above example I have set the LowMemoryThreshold to 512 MB hence the MEMPHYSICAL_LOW notification will be signaled as soon as the available memory drops to 512MB and HighMemoryResourceNotification (MEMPHYSICAL_HIGH) will be in signaled state till the available memory is  1536MB (LowMemoryThreshold *3).

After making the above change SQL Server will grow its Bpool memory till the available memory in the system is greater than 1536 MB and as soon as the available memory drops below 1536MB HighMemoryResourceNotification signal will be revoked by windows causing SQL Server to maintain steady state and will not grow its memory usage further but that  doesn’t  mean SQL Server will wait for the LowMemoryResourceNotification notification to scale down its memory usage after the  HighMemoryResourceNotification notification is revoked. SQL Server will always try to keep the available physical memory in the system high (I.e. SQL Server will try to keep the available memory in system to  HighMemoryThreshold (LowMemoryThreshold  * 3 ).

What if I have multiple instances of SQL Server on same server and how they load balance the memory among themselves?

SQL Server will try to balance to balance its memory usage with other instances of SQL Server running on the same box .  As I mentioned earlier SQL Server will try to maintain the available memory on the system to High memory threshold. SQL Server Lazy writer checks If there is disk reads performed in last 10 seconds and if there is no reads for last 10 seconds then SQL Server will reduce its memory usage until HighMemoryResourceNotification is signaled by OS.

Let us see this with an example :

Let us assume there are 2 SQL Server instances running on server with 32 GB of RAM and Lowmemorythreshold is set to 512MB on the system (so HighMemoryThreshold is 1536 (Lowmemorythreshold *3)).

1.            When the OS starts  HighMemoryResourceNotification  is set to on because there is adequate available memory on the server.

2.            SQL Server instance 1 starts first and it will consume memory till the HighMemoryResourceNotification resource notification is revoked ( HighMemoryResourceNotification  will be revoked when available memory drops below 1536 MB).

2.            Now the 2nd SQL Server instance is started and it finds High memory resource notification is revoked so it will not increase its memory usage. 

3.            Lazy writer thread of 1st instance checks if there is any disk reads performed by 1st Instance  in last 10 seconds , If there is no disk reads then first instance will scale down its usage until HighMemoryResourceNotification is signaled by OS (HighMemoryResourceNotification  will be signaled again when available memory becomes 1536 MB).

4.            2nd Instance which is hungry for memory sees the High memory resource notification and starts growing its usage till the high memory notification is revoked.  Once the high memory notification is revoked 2nd instance will stop growing.

5.            1st  instance finds the high memory notification is revoked and will again check if there are any disk reads in last 10 seconds and if there are no reads then It will further scale down till there is high memory resource notification.

6.            Once the high memory is signaled 2nd instance will start growing again.

7.            Over a time each instance will very well balance their memory requirements among themselves. ( I.e. if there is read performed from disk with in last 10 seconds we assume there is additional memory requirement for the instance so it will not scale down while on other hand if there is no reads for more than 10 seconds and if the memory available is below the high memory threshold instance it will scale down to give memory for other instance)  

8.            Instance with higher memory requirement will be consuming more memory than the instance with low memory requirement in some time. This way both the instances will balance their memory requirements with each other.


1. Above logic may not fit well if the total Physical memory on the system is very low compared with the memory requirements of multiple SQL Server instances running on the system because if you start the second instance while the first SQL Server is running with full memory utilization but still performing lot of reads I.e. RESOURCE_MEM_STEADY and still lot of reads , second instance may take long time or may not scale up its memory usage soon. In such case you can cap the max server memory but the performance of SQL Server will be very poor because of memory contention.

2. Also be cautious when you increase the value of LowMemoryThreshold beyond 512 MB.  Increasing this threshold  increases the range of memory that is available where neither the LowMemoryResourceNotification or HighMemoryResourceNotification object is signaled ( RESOURCE_MEM_STEADY).  So when you have multiple instance , if you start the second instance while the first SQL Server is running with full memory utilization and with lot of reads  I.e. RESOURCE_MEM_STEADY + reads continuously , second instance may take time scale up its memory usage soon and chances of getting Lowmemorythreshold is low because of wider range  of  RESOURCE_MEM_STEADY


1. What will happen when MTL allocations increases?

Available memory in system drops when the MTL consumption increases. If the MEMPHYSICAL_HIGH is set then there will not be any effect to bPool. If MTL consumption increases drastically it might cause available memory to drop further causing windows to trigger LowMemoryResourceNotification.

If LowMemoryThreshold is siganled SQL Server will scale down its bPool usage.

2. Will windows working set manager starts trimming the working set of all processes as soon as the LowMemoryResourceNotification is signaled?


3. What are the other effects of changing LowMemoryThreshold?

There might be other application and drivers which is also using memory notification from windows to grow and shrink memory usage. They will also shrink and grow when there is notification from windows.

4. Why would I need to CAP my SQL Server memory when we have a great dynamic mechanism in SQL Server to grow and shrink its memory usage?

You can leave the max server memory as default  If your operating system is Windows 2008 or above and if you have all the fixes in This link and This link and if you do not have any faulty drivers or applications which will request large amount of memory suddenly and if you are not using large pages memory model else I would suggest capping the Max server memory

If you have decided to configure the Max server memory remember it will not control the overall memory used by SQL Server. There are significant changes in memory allocations controlled by Max server Memory between SQL Server2012 and earlier versions. Let us understand what allocations it controls in SQL Server 2012 and earlier versions of SQL Server

What is controlled by SQL Server Max Server Memory (Extract from SQLServer2012 Memory) ?

SQL Server memory is internally divided in to two regions known as BPOOL and NonBPool (aka MTL or MTR) More details about BPOOL and MTL can be found in This blog.

In earlier versions of SQL Server (Till 2008 R2) “Max Server Memory” controlled the Maximum physical memory Single page allocator (BPOOL)  can consume in SQL Server user address space.

Only the single page allocator was part of BPOOL and Max server memory controlled only BPOOL, so the following allocations came outside BPOOL (Max server memory)

1.Multi-Page allocations from SQL Server [These are allocations which request more > 8 KB and required contiguous memory]

2.CLR allocations [These include the SQL CLR heaps and its global allocations created during startup]

3.Memory used for thread stacks within SQL Server process (Max worker threads * thread stack size). Thread stack size is 512K in 32 bit SQL Server, 904 K in WOW mode and 2 MB in 64-Bit 

4.Direct windows allocations made by Non-SQL Server dll’s ([These include windows heap usage and direct virtual allocations made by modules loaded into SQL Server process. Examples: allocations from extended stored procedure dll’s, objects created using OLE Automation procedures (sp_OA calls), allocations from linked server providers loaded in sqlserver process)

SQL Server 2012 memory manager has now clubbed single page allocator and multipage allocator together  as any-size page allocator . As a result, the any-size page allocator now manages allocations categorized in the past as single page and Multi-Page allocations.

1. "max server memory" now controls and includes “Multi pages allocations”.

2. In earlier versions of SQL Server CLR allocated memory was outside BPOOL (Max server memory)   . SQL Server 2012 includes SQL CLR allocated memory in "max server memory".

SQL Server 2012 "max server memory" configuration does not include only the following allocations:

1. Memory allocations for thread stacks within SQL Server process

2. Memory allocation requests made directly to Windows [Ex: Allocations (Heap, Virtualalloc calls ) from 3rd party Dll’s loaded in SQL Server process , objects created using OLE Automation procedures (sp_oa) etc]

Hope you got clarity on allocations controlled by  Max server memory , Let us see how to set it.

How to set correct value for SQL Server Max server memory?

There is no magic formula for this. Estimate the memory required by other applications running on same server, Operating system, Drivers , SQL Server Non- bPool allocations, jobs, anti virus etc.. Make sure you have acceptable available physical memory even when the system is under heavy load.

1. Consider the operating system memory requirement.

     Approximately 1 GB (Would increase if it is DC, cluster etc.)

2. Consider the memory requirements by other applications/processes running on the server.

   You have to derive it based on applications/processes/AV’s running on the system and their memory requirements. (Perfmon Process-> Private bytes and Working set can help)

3. Consider the memory requirements of the drivers/firmwares.

    You have to derive it based on memory requirements by drivers installed on the system. (RAMMAP can help)

4. Consider the NonbPool (aka MTL or MTR) memory requirements by SQL Server.

select  sum(multi_pages_kb)/1024 as multi_pages_mb from sys.dm_os_memory_clerks

(You can skip above query if your SQL Server version is 2012)


Max worker threads * 2MB


Memory for direct Windows allocations approximately 0 to 300 MB in most of the cases but you may have to increase it if there are many 3 party components loaded in SQL Server process (Including linked server dll’s, 3rd party backup dll’s etc.)


If you are using CLR extensively add some additional memory for CLR.   

5. Consider the memory requirement by jobs (Including replication agents, Log shipping etc. )  and packages that will run on the server.

You have to derive (May vary from few Mb’s to GB’s)

6. Consider SSAS and RS memory requirements.

You have to derive

7. Make sure there is good enough free space for operating system.

Approximately (100 MB for each GB till 4G) + (50 MB for each additional GB till 12GB) + (25 MB for each additional GB till your RAM size)

8. Other memory requirements.

If you have any other memory requirement specific to your environment.

Once you have calculated a reasonable value for all the above memory requirements  take the sum of all the above requirements and deduct it with total physical memory to derive an ideal value for your max server memory.

Max server memory=  Total physical memory  – (1+2+3+4+5+6+7+8)

If you still see LowMemoryResourceNotification  or working set below 100% frequently then use This exe which will print the memory information of all the processes and system wide memory information (Global memory status) when the operating system signals  low memory notification. Once you get the output from the exe when there is LowMemoryResourceNotification  review requirements of each process and tweak Max server Memory accordingly.

Important: Make sure you have this fix if you are on windows2003

What about Min server memory and should I configure it?

I mentioned earlier that when LowMemoryResourceNotification comes from Windows or HighMemoryResourceNotification is revoked+No reads for 10 seconds , SQL Server scales down its memory usage.

How much it scales down?

Until “Minimum server memory”  is reached (If there is continuous memory pressure on the system).

What happens when you set Max server memory and min server memory to same value?

SQL Server will never scale down its memory usage even when there is memory pressure system wide (Lowphysicalmemory notification  set at system level). Note: This setting does not affect OS from paging.

What are the affects?

When there is LowMemoryResourceNotification  If LPM is not enabled SQL Server’s working set (Bpool + Non bPool )will be paged. If LPM is enabled system will starve for memory  and non-bpool will be paged.

If you do not want SQL Server to scale down its usage when there is LowMemoryResourceNotification  in windows configure Min server memory and Max server memory to same value (Bad choice). 

If you want to limit  “how much SQL Server wants to scale down“ you can configure this value.

If you liked this post, do like us on Facebook at and join our Facebook group


Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter


The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights


38 Responses to “Max server memory – Do I need to configure?”

  1. Vijay said

    Nice coverage of a key configuration needed to respond to intensive operations. Reporting Services memory can also be configured if using that

  2. velmani said

    mind blowing blog…I have a doubt .. I have seen the sql server replication agents distrib.exe ,logread.exe,snapshot.exe utilised around 10 MB each(around 100 distrib agents in that server) in one of our distributor server…. Will that memory comes in max memory allocated to SQL or will it utilise the OS memory?

  3. Jacky said

    thank you for your great article, I have a question .
    “Will windows working set manager starts trimming the working set of all processes as soon as the LowMemoryResourceNotification is signaled?”
    you answered “No”,so what’s point or when the windows working set manager starts trimming the working set of all processes ?

    • Calculation is not straight forward and there are different scenarios, but generally when page fault is high or free list becomes thin working set manager is waked for trim.

  4. velmani said

    Is sql server restart clears anything in buffer pool (data\index, memory objects for SP plan, cachstore_sqlcp,cachstore_objcp etc )?

    If there are two connections executing different SP’s refferring some common data\index pages, will the page be referred twice in buffer ?

    • For question 1. Yes everything will flushed from cache and re cached(data/index pages) / recreated (Plans and memory objects) when they are needed. For question 2. No will be shared by sessions.

  5. Excellent article, Karthick! You’ve covered almost all the aspects in-depth. I especially liked the section on how to calculate appropriate value for max server memory. Hope many will get benefited with this and can correctly configure SQL memory. Keep up the great work!

  6. velmani said

    If I kill all the connections in sql server, will the proc cache be flushed off?

    because after mirror roll, in the mirror (old principal server) one of memory clerks(MEMORYCLERK_SQLGENERAL) utilisation was reduced from 5 Gb to 400MB ?

    Is there threshold time limit for any of the below clerks to stay in cahe or only will they clear off upon sql restart, recompile etc..


  7. […] If you would like to derive the value for max server memory and minimum server memory for sql server   follow […]

  8. […] Max server memory – Do I need to configure? […]

  9. […] Max server memory – Do I need to configure? […]

  10. […] Max server memory – Do I need to configure? […]

  11. Chandrashekar said

    Really an awesome article I ever come across. Almost got every info which I was looking with respect to SQL Server Memory management. Thank you.


  12. Hello! I’ve been following your web site for some time now and finally got the
    courage to go ahead and give you a shout out from Austin Tx!
    Just wanted to mention keep up the good work!

  13. Take initiative and get the amount of customers you deserve.
    Constantly run 2 ads on Adwords for every ad group.
    Why would you entrust them with a matter as sensitive as SEO for your website.

  14. Deegan was arrested and charged with human trafficking and drug possession after
    cops busted in and found the girls being held hostage.

    They will do all the talking for you with the agent. This method pts are
    crucial inside working with a defined talent management strategy.

  15. Take for instance a company that for the current period has more
    outstanding payables than they do receivables. Educating these customers in how
    to handle their tax affairs in the future can save them many thousands of dollars.
    Itemized deductions and personal exemptions do not affect self-employment taxes.

  16. Your mouth watering chicken salad is ready to serve.
    This can be because your system is using reserve
    excess fat and converts these fats into usable power.
    What is really amazing about this type of treatment is the fact that it gets you rid of that hard
    to lose fat first.

  17. These weapons do make their appearance in some of the classic ninja movies of the 1980s.
    As the title of this article indicates, we have to focus on more details
    about the BEST self defense items. I’m watching you do your thing and it’s flashing to the convenience store fight with the bicycle
    and it is just amazing.

  18. At first the credit will reduce that taxed owed,
    but if there is no tax then the money will be given to the taxpayer.
    In this article I guide the students that how they can pass the
    exams and how can they get the certification for the latest
    knowledge this certification exam students click at accounting firms uk or visit its Limited company formation in uk its better for your bright future and will helpful to attain the
    IT certification for more information touch with
    me. (e)   The availability of adequate technical, financial and other resources to complete the development and to use or sell the asset.

  19. Thank you foor the auspicious writeup. It inn fact was
    a amusement accounht it. Look advanced tto more added agreeable from you!
    However, hhow can we communicate?

  20. Outstanding post however I was wanting to know if you could write a litte more
    on this topic? I’d be very thankful if you could elaborate a little bit further.

  21. I know this if offf topic but I’m looking into starting my own weblog andd was wondering
    what all is needed to get set up? I’m assuming having a blog like yours would
    cost a pretty penny? I’m not very internet smart so I’m
    not 100% sure. Any tips or advice would be greatly appreciated.
    Many thanks

  22. Wilhemina said

    Hmm is anyone else having problems with the pictures on this blog loading?
    I’m trying to determine if its a problem on my end or if
    it’s the blog. Any feedback would be greatly appreciated.

  23. Analisa said

    When someone writes an article he/she maintains
    the thought of a user in his/her mind that how a user can know it.
    Thus that’s why this article is outstdanding. Thanks!

  24. This site was… how do you say it? Relevant!!

    Finally I have found something which helped me. Thanks!

  25. Krishnan said

    Hi Karthik,

    there are two instances(2008) are running..Mem is using 97%
    Total mem -64 GB
    Default instance mem allocated – 32 GB
    Named instance mem allocated -28 GB
    I’ve checked in process both are using huge volume of memory
    Even checked there is no opentran() and no long running job on that both instances
    CPU is only 5 % utilizing
    Please provide a solution to reduce the mem

  26. Dang! Thanks just for expending your time frame
    to discuss this with us.I really enjoyed the read and look foward to future posts!

  27. AlbertZOZ said

    Thanks Karthick for your great article!

    I did experience the LowMemoryResouceNotication in our environment, which caused 100 CPU usage.
    The detailed story is we have 10 sql server instances on windows 2012 R2 with 512GB ram, each instance has 10 AlwaysOn group, each group has 10 databases. Each instance has min 49GB and max 50GB assigned, and we are migrating the database to this environment, so the file copy somehow consumed windows memory, When we finishedm, there is only 3 GB available memory left, and CPU usage kept on 90-100%, got xperf dump, which indicates LowMemoryResouceNotication is the root cause, then we reset the min and max value for one instance, the problem is gone now.

    To make myself more clear I want to reproduce the issue, since we have 512GB ram(max 50GB for 10 instances, so max 500GB Sql Server can use), the lowmemorythreshold is 8GB. I tried to copy some big files from network, so the available memory drops below 8GB(only 5-7 GB available memory), but nothing happened, I reckon the memory pressure doesn’t last long enough, or 5-7GB doesn’t look like a pressure, so windows doesn’t do anything, I wanted to push the available memory down to 3GB but it is online already, so I didn’t try.

  28. […] As you already know there is No general formula to calculate max server memory you can do some quick maths and reach to a value but still you would need help of Perfmon counters at last to monitor memory usage and change accordingly. I know below general formula and I use it as well. I learned this formula from This Link […]

  29. said

    Simply wish to say your article is as amazing.
    The clarity on your put up is just nice and i can think you’re a professional on this subject.

    Well together with your permission allow me to
    snatch your feed to stay updated with imminent post.

    Thanks a million and please carry on the enjoyable work.

  30. karnisze said

    My brother recommended I may like this website. He used to be entirely right.
    This post truly made my day. You cann’t believe just how so much time I
    had spent for this information! Thanks!

  31. I logged about 30. This was initially ever camp and I
    didn’t would like to blow up by Wednesday and shorted the Ridge because of time constraints.
    Up coming time need to push it to about 40-45.

  32. Bharat Bhuva said

    Thank you Karthick for your great article!
    Your detail explanation is amazing.

  33. […] Max server memory – Do I need to configure? « MSSQLWIKI – Do I need to configure Max server memory and min server memory? What is the right value for this configuration and how to determine it? There are many debates around … […]

  34. economy home care

    Max server memory

  35. yoko said


    blog topic

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: