THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Setting max server memory

If there is one server setting that is close to universal to configure, then it is probably the "max server memory" setting. The setting is documented here. There are plenty of articles out there on this subject. The purpose for this article is for me to have somewhere to point when I get the question: "What value should I set this to?". I usually refer to Jonathan Kehayias' blog post when I get this question: http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/. For starters you want a simple formula to begin with, and then some hints on what to monitor if you want to fine-tune the value. Jonathan's articles provide both. The simple formula for how much to reserve for the OS is:

1 GB
Plus 1 GB for every 4 GB in the machine, between 4 and 16 GB
Plus 1 GB for every 8 GB in the machine, above 16 GB

And here's a TSQL script if you don't want to do the math yourself. Note that you need to specify how much memory you have in the machine.

 


--Based on Jonathan Kehayias' blog post:
--http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

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

DECLARE
@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------------------------------------------------------------------------------------------------
IF CAST(LEFT(CAST(SERVERPROPERTY('ResourceVersion') AS VARCHAR(20)), 1) AS INT) = 9
  
SET @sql = 'SELECT physical_memory_in_bytes/(1024*1024*1024.) FROM sys.dm_os_sys_info'
ELSE
   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'
  
ELSE
     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
EXEC(@sql)
SET @memInMachine = (SELECT MAX(mem) FROM #mem)

--Calculate recommended memory setting---------------------------------------------------------------------------------
SET @memOsBase = 1

SET @memOs4_16GB =
  
CASE
    
WHEN @memInMachine <= 4 THEN 0
  
WHEN @memInMachine > 4 AND @memInMachine <= 16 THEN (@memInMachine - 4) / 4
    
WHEN @memInMachine >= 16 THEN 3
  
END

SET
@memOsOver_16GB =
  
CASE
    
WHEN @memInMachine <= 16 THEN 0
  
ELSE (@memInMachine - 16) / 8
  
END

SET
@memOsTot = @memOsBase + @memOs4_16GB + @memOsOver_16GB
SET @memForSql = @memInMachine - @memOsTot

--Output findings------------------------------------------------------------------------------------------------------
SELECT
@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

 

 

Edit 1 2014-03-06: Got the memory in the machine from sys.dm_os_sys_info, suggested by Ola Hallengren.

Edit 2 2014-03-20: Adjusted script to work on 2008R2 and lower, as suggested by Shanky. Also added current mem config to output. Changed output from PRINT to SELECT (to facilitate multi-server query window).

Edit 3 2014-03-22: Adjusted script to support 2005, as suggested by Steve Meder. Also changed to only one resultset.

Edit 4 2014-05-30: Fixed some bugs for 2005, reported by Lee Linares.

 

Published Thursday, March 06, 2014 10:27 AM by TiborKaraszi

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

praveen said:

HI Experts,

My database server memory utilisation is growing faster from past 1 week. it remained same for 1 week around 55% and now it is going to 70% and increasing.

Total OS memory is 32GB and I kept cap for sql server memory upto 29GB. Dont know what to do..

Please suggest...

Thanks

Praveen

March 6, 2014 7:08 AM
 

Allen McGuire said:

Praveen - SQL Server will take as much memory as you give it.  It's not uncommon for a database server to be at 90%+ memory utilization all the time.  What are the sizes of your databases?  What is the page life expectancy?

March 6, 2014 11:03 AM
 

ihafidh said:

Nice post.

Question on the script:

If I have 8 GB of total memory, I calculated that to be 1 GB plus 2 GB (1 GB for every 4 GB) = 3 GB for the OS.

However, when I plug that into your script, I get 2 GB for the OS. Is my math wrong?

March 6, 2014 12:39 PM
 

TiborKaraszi said:

ihafidh, No, not 1 GB for every 4 GB. 8GB means 1 GB to begin with and 1 GB for every 4 GB between 4 GB and 16 GB. You have 8 GB, which means you have one such "slice" of 4 GB between 4 GB and 16 GB. I.e., 1+ 1 = 2. :-)

March 6, 2014 1:55 PM
 

Scott Duncan said:

@ihafidh

The first 1GB covers the first 4GB of memory. You then have 1 block of 4GB above 4GB, so that gets 1GB. Hence 2GB.

The 1GB for every 4GB is for memory amounts _above_ 4GB up to 16GB.

March 6, 2014 2:20 PM
 

Jose M. said:

Have you taken into consideration the number of workers that depends on the number of cores (two mb each core) ? Your recommendation is far for my experience that is between 10% 15% of the server memory with a minimum of 2 GB and it has been always enough.

March 6, 2014 5:11 PM
 

Uri Dimant said:

Hi Tibor

I think for the cluster environment we need to choose the different approach so if the one node fails over the second one we would have some script in place to adjust the max memory settings...

March 9, 2014 8:08 AM
 

TiborKaraszi said:

Hi Uri. I assume you mean a cluster when you have more than one instance. Yes, if you normally have one instance on a node and then bacause of a fileover happens to have two instance, then you will have to adjust for that. In that case, you have several inatnces on a machine, which I explicitly commented in the code I posted. :-)

March 9, 2014 1:10 PM
 

Shanky said:

Hi Tibor,

Physical_memory_Kb column is not a valid column in DMV sys.dm_os_sys_info actually column name is physical_memory_in_bytes.You should change this in your script

Good formula Indeed but I would like you to mention a caution when Actual memory available with Windows Box is less ,then this might little be dangerous.If machine has 8 G formula will give 3 G for OS,but i guess it might be less in some case.Like if memory is 5 G giving 2 G to OS seems not good.Actually it depends is correct answer

March 19, 2014 12:10 PM
 

TiborKaraszi said:

Hi Shanky,

The column changed name between 2008 R2 and 2012. I changed the script to handle that. Thanks.

I however don't want to touch the actual formula. The main reason for this blog was to have somewhere to point to when I get this question. I initially thought of only pointing to Jonathan's blog post. But then I decided to add some TSQL to it, but I still feel like leaving the calculations as per Jonathan's original calculation.

March 20, 2014 9:37 AM
 

Shanky said:

You are correct.But stil I advise user to use perfmon to just cross check if if calculations are correct.Just in case .A second thought to make formula full proof.

Anyways Nice blog...:)

March 20, 2014 12:31 PM
 

TiborKaraszi said:

Agreed. It serves as a starting point, and should be adjusted for edge cases etc. :-)

March 20, 2014 12:56 PM
 

Lee Linares said:

Thanks for posting the article and code. I appreciate your time and effort.

I'm confused by the difference in the output for SQL 2005 and SQL 2008. I have a SQL 2005 instance and a SQL 2008 instance BOTH with  physical_memory_in_bytes = 17179402240 (32GB).

In the output for the SQL 2008 instance I get memForOS = 3.75 and memForSql=11.25.

For the SQL 2005 instance I get  memForOS = 2049.88 and memForSql=14333.12.  The 14333.12 value when converted to GB is 13.99GB. That's a difference of 2.74GB

Any ideas on why the big difference? Or am I just missing something? Thanks,

Lee

May 29, 2014 3:19 PM
 

TiborKaraszi said:

Thanks Lee. Can you try now? I had a couple of bugs in there, hopefully fixed now. I don't have a 2005 instance to try this on, so it would be great if you give me a hint whether it seems OK now, or not... :-)

May 30, 2014 4:38 AM
 

Lee Linares said:

Tibor,

Beautiful!! Runs perfectly now. Thanks again, so very much. I have run it against 60 instances and identified several instances that I am investigating closer before submitting Change Management Requests. Thanks.

   Lee

May 30, 2014 2:43 PM
 

TiborKaraszi said:

Glad to hear that, Lee. Thanks for the bug report and the feedback

June 1, 2014 5:40 AM
 

Dhruv Sampat said:

Don't forget to Show Advanced options when you get the recommendation.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'max server memory', 12288;

GO

RECONFIGURE;

GO

October 21, 2014 10:22 PM
 

TiborKaraszi said:

Good point, Dhruv!

October 22, 2014 4:16 AM
 

Bob Hansen said:

Thanks very much Tibor, very informative.  I have a SQL2008 Server with 256gb and sql max mem is set to 200gb  We have a collection using the dmvs that shows os_available_physical_memory_kb is hovering around 40GB, so the OS is using about 16GB,  (256-200-16 = 40GB free)  Knowing that would it be safe to increase SQL mem to 225 or 230gb ?

Thanks Very Much!!

October 28, 2014 10:51 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement