Cool... Now we have a calculator for finding out a Max. Server Memory value

 

 

 

 

 

 

Cool... Now we have a calculator for finding out a Max. Server Memory value

Rate This
  • Comments 4

This calculator is now withdrawn

since I'm not able to invest my time

to improve it to make it "stay current".

Apologize for the inconvenience!

 

Hello SQL Community,

I have written a simplified “Max. Server Memory” calculator for calculating "Max. Server Memory" value for 64-bit SQL Server instances after seeing my blog referenced in MSDN forums, blogs etc., to automate the calculation of Max. Server Memory.

Few things to take care before you use this calculator:

IMPORTANT: This calculator is designed to help Novice SQL Database Administrators or someone who manages SQL Server but is really not an expert in SQL Server. So to put it simple, this calculator is meant for SQL Server professionals with Level 100 knowledge in SQL Server

Based on my support experience, lot of performance & memory related problems are because customer’s don’t set a Max. Server Memory and they don’t do that because they don’t know what to set. This is my personal interest to help those people who are in need but confused because there are so many recommendations around how to calculate & set this value.

Lot of other things like software based replication, HBA card, Fusion IO card, Antivirus Real-time scanning, Firewall monitoring the traffic flow through ports etc., need good amount memory which is not accounted here simply because we don't see these in all the Environments. If you use anything other than the features given in the calculator here, please make sure you adjust the Max. Server Memory accordingly!!!

So important thing is, take the Max. Server Memory value recommended here as a reference value and then manipulate the value according to your custom environment and then configure Max. Server Memory in SQL Server accordingly. This is not all, please refer to following instructions on how to monitor memory usage of SQL Server & OS and make sure you monitor the performance of the server during peak load to see whether the value is right or needs a change so that you don't hurt  the performance of other applications & Operating System!!!

This calculator will not help on server having more than 1 instance of SQL Server because the memory required for OS, SSIS, Antivirus etc., are shared so the calculation becomes little complex.

Max. Server Memory value shown here is not to be assumed as an official Microsoft recommendation

This calculator is applicable only for 64-bit SQL Server

Things have changed for SQL Server 2012 so this calculator is applicable only for SQL Server 2005, 2008, 2008 R2

If you think you know how to arrive at this value better than what is shown in the calculator, you can share the feedback in the comments section or skip the recommendations because you are not the right audience to use this calculator.

Max Worker thread calculation used to arrive at memory requirement for worker threads is based on Books On Line - http://bit.ly/KflAa8

 You can monitor the health of SQL Server Memory using these Performance monitor counters:

Page Life Expectancy Counter under SQL Server Buffer Manager which is the average number of seconds a page stays in cache. It should be atleast = 300 * (Total RAM in GB)/4. Though our recommendation earlier was PLE should be 300, if a page is staying just for 5 minutes on a server with 128 GB RAM then it is not a right value to say that there is no memory pressure.
Memory Grants Pending, in the Perfmon object SQL Server Memory Manager (Close to 0 or lower is better) (OR)
Memory grant queue waits in the Perfmon object SQL Server Wait Statistics Object (Close to 0 or lower is better)

To Monitor the health of memory available for other processes & OS:

Available MBytes under Perfmon object Memory - Refer http://blogs.msdn.com/b/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx for some reference values

More information on troubleshooting memory bottleneck is available at http://technet.microsoft.com/en-us/library/cc966401.aspx

Max server memory setting only controls size of the Buffer Pool and hence doesn't affect allocations going through Multi Page Allocator (MPA). You can monitor your  Multi Page Allocator usage by using sys.dm_os_memory_clerks DMV and adjust the Max. Server Memory accordingly.

So here we go

Calculator: http://blogs.msdn.com/b/sqlsakthi/p/max-server-memory-calculator.aspx

UPDATE: Per feedbacks from MVP's over twitter and blogs, modified the calculator to give more meaningful value for memory required by Operating System since we see more systems with high Physical memory loaded.

Formula used to calculate memory required for OS: 2GB + 12% of physical memory. (Thinking of how or why this value? It is 2 GB base and 7% for kernel pool (paged pool, NPP and PFN database) and 5% for cache.)

Technology used: Office Web Apps (Try it for free) & MSDN Static Pages.

You can download the Excel to view the formula used to arrive at the Max. Server Memory value.

Note:

Number of processors visible to SQL Server may not be equal to number of processors available in the server. Thinking why? these links will help:

http://blogs.msdn.com/b/psssql/archive/2012/01/23/assigning-sql-server-sql-agent-to-a-processor-group-oom-hang-performance-counters-always-zero-for-buffer-pool.aspx

http://blogs.msdn.com/b/psssql/archive/2009/06/16/sql-server-on-windows-7-windows-2008-r2-with-more-than-64-cpus.aspx

Post your feedback and comments in this blog post!

You may also try MAXDOP calculator available at http://blogs.msdn.com/b/sqlsakthi/archive/2012/05/24/wow-we-have-maxdop-calculator-for-sql-server-it-makes-my-job-easier.aspx

Sakthivel Chidambaram, Microsoft

Leave a Comment
  • Please add 1 and 2 and type the answer here:
  • Post
  • Hi, Sakthivel.  That's a really interesting idea to do this with Excel Services, but I ***strongly*** disagree with the results.  For example, you're not asking if the server has host bus adapters (HBAs) or FusionIO storage, both of which require a lot of free memory for the drivers.  You're not asking if there's any drivers or agents that need memory (like monitoring software).  I'm really worried about what would happen if people followed this advice without asking more questions.  I would strongly, strongly recommend that you allow comments on that page and ask users to post additional things that may require memory, or at least point them to this blog post to add comments.  I was so upset with the configurator that I sought out this page to leave the comment, but not everyone will take the time to do that.

    Reply from Sakthi

    Brent: I have addded a detailed disclaimer to make sure anyone who is going to use the calculator understands why is this released/who is the audience/what to do after using this calculator. Also link to post comments is given in the calculator page.

  • Feedback on the calculations and how incorrect they are:

    sqlskills.com/.../Wow-An-online-calculator-to-misconfigure-your-SQL-Server-memory.aspx

    Reply from Sakthi:

    Jonathan: Your feedback on OS Memory calculation has been taken care.

  • Hi, Sakthivel,

    As I remembered that you had a very good post on how to make calculation on Max Server memory. But for this calculator,I think that you might need to do more work on to it. I was thrilled to try but it turned out not working very well, particulary those excel fomulars are just too general and simple. but thanks for you initiative.

    Reply from Sakthi:

    Thanks Steven! Sure, I'm going to work on enhancing this based on the feedbacks I hear from the community & my colleagues. Please let me know in specific about what is missing/lacking... 

  • i need calculator for finding out a Max. Server Memory value for sql server 2000. pls help.

Page 1 of 1 (4 items)