Do you often build and configure SQL Server instances of various sizes? What formula have you been using for coming up with a value for the ‘max server memory (MB)’ server configuration option? Or do you simply pick a reasonable number based on the RAM size?
The ‘max server memory (MB)’ option lets you specify the upper limit for SQL Server buffer pool. You don’t want to set it too low as that limits the amount of data that can be cached and leads to performance issues. You also don’t want to set it too high, causing SQL Server process to hog the memory and cause the Operating System (OS) to struggle.
What is my current approach to max server memory setting?
I have been building, configuring and installing SQL Servers for several years. I usually set the ‘max server memory (MB’ parameter on my SQL Servers to 90% of the availabe RAM. For example, on a machine with 512 GB RAM, I would restrict max server memory to 460 GB, leaving a healthy 51 GB for Windows to manage the host. Over the years, this has served me well. I do monitor my SQL Server instances for any signs of memory pressure or memory related errors. If I do see any memory issues, I adjust the max memory setting up or down depending on the situation.
What is Microsoft recommending?
Historically Microsoft have not specified a formula or percentage for coming up with a suitable value for ‘max server memory (MB)’ setting – but that all changed, when Microsoft published a page title “Server memory configuration options“. In this page Microsoft is recommending to go with 75% of RAM (system memory) for SQL Server max server memory setting – which means, leaving 25% of RAM to the OS. With this recommendation, using the same example as before, on a server with 512 GB RAM, you would have to restrict SQL Server max server memory to 384 GB, leaving 128 GB to the OS. That’s quite a lot of RAM to be leaving to Windows. What do you think? Would you go with this recommendation or are you already following this guideline? Share your thoughts by leaving a comment below.
What is SQLSkills.com recommending?
Yes, the popular SQL Server website SQLSkills.com have been recommending a different formula for calculating the value for max server memory. You can see this here: How much memory does my SQL Server actually need? In short, they recommend “Reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM – then monitor“. Sounds interesting and obviouslt comes up with a different value for max server memory than 90% or 75% of RAM options presented above.
Any other recommendations?
If you think giving 90% of RAM to SQL Server is too much or restricting SQL Server to 75% of RAM is too low, and not very sure on SQLSKills recommendation – I have a different option here – I am only half serious here – if you are feeling brave, go with an average value from all the above recommendations.
Let’s see how these recommendations stack up:
Here’s a graphic showing the max server memory recommendations produced by the above formulae. It can be used as a reference guide or a cheat sheet to quickly look up the recommended values.
Stored proc for setting ‘max server memory (MB)’ automatically using the above formulae:
I’ve come up with a stored procedure named dbo.SetMaxServerMemory, which incorporates all four of the above mentioned formulae for coming up with a ‘max server memory (MB)’ recommendation.
Some key points about this stored procedure dbo.SetMaxServerMemory:
This stored procedure can be used to just display the recommended max server memory values OR to actually apply the value recommended by a chosen formula – by automatically running sp_configure.
It takes two input parameters:
- @AlternateRAMSize_GB – Optional – If not specified, the stored procedure will automatically get the amount of RAM installed on your SQL Server host and use it for calculating the optimal max server memory values. However, you can specify a different RAM size as input to this stored procedure, to see what the recommended max server memory values would be, for that RAM size.
- @ApplyFormula – Optional – takes either NULL, 1, 2, 3, or 4. If not specified, it will simply display the recommended values for max server memory. You may specify 1, 2, 3 or 4, to actually update the ‘max server memory (MB)’ server parameter by running sp_configure automatically. 1 sets max server memory to 90% of RAM, 2 sets it to 75% of RAM, 3 applies the formula from SQLSkills.com, and 4 sets it to an average of values recommended by formulae 1, 2 and 3.
Even if you specify a value for @ApplyFormula, it will only be applied if actualy RAM size was used in the calculations – meaning, if you specied a different value to @AlternateRAMSize_GB the stored procedure will work in ‘display only’ mode. In order to update max server memory leave @AlternateRAMSize_GB as NULL, and let the stored procedure work with actual RAM size.
As a precaution, dbo.SetMaxServerMemory will automatically handle the case of the calculated max server memory value being smaller than the current ‘min server memory (MB)’ value, and fail.
Usage examples for dbo.SetMaxServerMemory:
/********** Example stored procedure calls **********/
-- Run the stored proc in display-only mode,
-- to see the recommended max memory values using the 4 formulae - based on actual RAM
EXEC dbo.SetMaxServerMemory
-- To get the recommended 'max server memory (MB)' values for a RAM size of 750 GB,
-- regarldess of the current RAM size of the server
EXEC dbo.SetMaxServerMemory @AlternateRAMSize_GB = 750
--To update the 'max server memory (MB)' setting to 90% of the server RAM size:
EXEC SetMaxServerMemory @ApplyFormula = 1
--To update the 'max server memory (MB)' setting to 75% of RAM size (MS recommendation):
EXEC SetMaxServerMemory @ApplyFormula = 2
--To update the 'max server memory (MB)' setting using the SQLSkills.com formula:
EXEC SetMaxServerMemory @ApplyFormula = 3
--To update the 'max server memory (MB)' setting with average value of all three formulae:
EXEC SetMaxServerMemory @ApplyFormula = 4dbo.SetMaxServerMemory:
IF OBJECT_ID('dbo.SetMaxServerMemory', 'P') IS NOT NULL
BEGIN
RAISERROR('Stored procedure dbo.SetMaxServerMemory already exists. Recreating', 0, 1)
DROP PROC dbo.SetMaxServerMemory
END
GO
CREATE PROC dbo.SetMaxServerMemory
(
@AlternateRAMSize_GB numeric(15, 2) = NULL,
--If NULL, Actual RAM size will be used from sys.dm_os_sys_memory
@ApplyFormula tinyint = NULL
/*
1 - 90% of RAM for SQL Server
2 - 75% of RAM for SQL Server (Microsoft recommendation)
3 - SQLSkills formula
1 GB
+ 1 GB of RAM for each 4 GB of RAM from 4-16 GB
+ 1 GB of RAM for each 8 GB RAM above 16 GB RAM
4 - an average of all the above formulae
*/
)
AS
BEGIN
SET NOCOUNT ON
-- Created by Vyas Kondreddi
-- https://dutydba.com
DECLARE @CurrentSQLMaxMemory numeric(15, 2), @NoEXEC bit = 0, @Command nvarchar(64),
@NewMaxServerMemory_MB numeric(15, 2), @MinServerMemory_MB numeric(15, 2)
IF (@AlternateRAMSize_GB IS NOT NULL) AND (@ApplyFormula IS NOT NULL)
BEGIN
SELECT 'Max server memory won''t be updated, as an alternate RAM size is specified rather than actual RAM'
SET @NoEXEC = 1
END
SET @AlternateRAMSize_GB =
COALESCE(@AlternateRAMSize_GB, (SELECT total_physical_memory_kb/1024/1024. FROM sys.dm_os_sys_memory))
SET @CurrentSQLMaxMemory =
(
SELECT CAST(CAST(value_in_use AS bigint)/1024. AS numeric(15, 2))
FROM sys.configurations
WHERE name = 'max server memory (MB)'
)
DECLARE @Commands table
(
FormulaID int PRIMARY KEY,
TotalRAM_GB numeric(15, 2),
CurrentMaxSQLMemory_GB numeric(15, 2),
Formula varchar(64),
Recommended_GB numeric(15, 2),
LeftForOS_GB numeric(15, 2),
Command varchar(64)
)
IF @AlternateRAMSize_GB < 2
BEGIN
RAISERROR('This procedure is designed for SQL Servers with at least 2 GB of RAM', 11, 1)
RETURN (-1)
END
IF (@ApplyFormula IS NOT NULL) AND (@ApplyFormula NOT IN (1, 2, 3, 4))
BEGIN
RAISERROR('The value for @ApplyFormula must be either 1, 2, 3 or 4', 11, 1)
RETURN (-2)
END;
WITH MemCTE AS
(
SELECT
CAST(@AlternateRAMSize_GB * .9 AS numeric(15, 2)) AS [90%],
CAST(@AlternateRAMSize_GB * .75 AS numeric(15, 2)) AS [75%],
CAST
(
@AlternateRAMSize_GB -
(
1 +
CASE WHEN @AlternateRAMSize_GB >= 4 AND @AlternateRAMSize_GB <= 16
THEN (@AlternateRAMSize_GB - 4) / 4.
ELSE 0
END +
CASE WHEN @AlternateRAMSize_GB > 16
THEN 3 + ((@AlternateRAMSize_GB - 16.) / 8)
ELSE 0
END
) AS numeric(15, 2)
) AS [SQLSkills]
)
INSERT INTO @Commands
(
FormulaID,
TotalRAM_GB,
CurrentMaxSQLMemory_GB,
Formula,
Recommended_GB,
LeftForOS_GB,
Command
)
SELECT
1 AS Command,
@AlternateRAMSize_GB AS TotalRAM_GB,
@CurrentSQLMaxMemory AS CurrentMaxSQLMemory_GB,
'90% - Basic Formula' AS Formula,
[90%] AS MaxSQLMemory_GB,
@AlternateRAMSize_GB - [90%] AS LeftForOS_GB,
'EXEC sp_configure ''max server memory (MB)'', '
+ CAST(CAST([90%] * 1024 AS bigint) AS varchar(16))
+ '; RECONFIGURE;' AS Command
FROM MemCTE
UNION ALL
SELECT
2 AS Command,
@AlternateRAMSize_GB AS TotalRAM_GB,
@CurrentSQLMaxMemory AS CurrentMaxSQLMemory_GB,
'75% - Microsoft Formula',
[75%],
@AlternateRAMSize_GB - [75%] AS LeftForOS,
'EXEC sp_configure ''max server memory (MB)'', '
+ CAST(CAST([75%] * 1024 AS bigint) AS varchar(16))
+ '; RECONFIGURE;'
FROM MemCTE
UNION ALL
SELECT
3 AS Command,
@AlternateRAMSize_GB AS TotalRAM_GB,
@CurrentSQLMaxMemory AS CurrentMaxSQLMemory_GB,
'SQLSkills Formula',
SQLSkills,
@AlternateRAMSize_GB - SQLSkills AS LeftForOS,
'EXEC sp_configure ''max server memory (MB)'', '
+ CAST(CAST(SQLSkills * 1024 AS bigint) AS varchar(16))
+ '; RECONFIGURE;'
FROM MemCTE
UNION ALL
SELECT
4 AS Command,
@AlternateRAMSize_GB AS TotalRAM_GB,
@CurrentSQLMaxMemory AS CurrentMaxSQLMemory_GB,
'Average of Formulae 1, 2 & 3',
CAST(([90%] + [75%] + SQLSkills)/3 AS numeric(15, 2)),
CAST(@AlternateRAMSize_GB - (([90%] + [75%] + SQLSkills)/3) AS numeric(15, 2)) AS LeftForOS,
'EXEC sp_configure ''max server memory (MB)'', '
+ CAST
(
CAST
(
CAST(([90%] + [75%] + SQLSkills)/3 AS numeric(15, 2))
* 1024 AS bigint
) AS varchar(16)
)
+ '; RECONFIGURE;'
FROM MemCTE
SELECT * FROM @Commands ORDER BY FormulaID ASC
IF @ApplyFormula IS NOT NULL AND @NoEXEC = 0
BEGIN
SELECT
@Command = Command,
@NewMaxServerMemory_MB = Recommended_GB * 1024.
FROM @Commands
WHERE FormulaID = @ApplyFormula
SET @MinServerMemory_MB = (SELECT CAST(CAST(value_in_use AS bigint) AS numeric(15, 2)) FROM sys.configurations WHERE name = 'min server memory (MB)')
IF @NewMaxServerMemory_MB < @MinServerMemory_MB
BEGIN
SELECT
'Uable to continue' AS Error,
@NewMaxServerMemory_MB AS MaxServerMemoryMB,
@MinServerMemory_MB AS MinServerMemoryMB
RAISERROR('max server memory must be greater than or equal to min server memory', 11, 1)
RETURN(-3)
END
ELSE
BEGIN
SELECT 'Applying formula #' + CAST(@ApplyFormula AS varchar(1)) + ': ' + @Command AS Outcome
EXEC sp_executesql @Command
END
END
ELSE
BEGIN
SELECT 'No changes made' AS Outcome
END
END
GO
IF OBJECT_ID('dbo.SetMaxServerMemory', 'P') IS NOT NULL
BEGIN
RAISERROR('Stored procedure dbo.SetMaxServerMemory has been created successfully', 0, 1)
END
GOBelow screenshot shows the sample output from dbo.SetMaxServerMemory
Additinoal notes:
Scenario of multiple SQL Server instances on a host: Do not rely on the output of this stored procedure in multi-instance SQL Server installations. In this scenario, not all instances require the same max memory setting, and the workloads would be different too. As a DBA, you have a to make a call on how much memory to give to each instance of SQL Server, making sure you are still leaving enough memory of the operating system to run smoothly.
Monitoring for memory issues: Keep an eye on the \Memory\Available MBytes performance counter in Performance Monitor (System Monitor) to make sure the available free RAM at host level is not getting too low. You could also monitor information presented by the system DMV sys.dm_os_sys_memory. Additionally, investigate any memory related error messages and warnings from SQL Server error log and take appropriate remedial action.
Automation: It is possible to make dbo.SetMaxServerMemory an auto startup stored procedure, by marking it as such using sys.sp_procoption, and setting the startup option to on. You might want to wrap up the call to dbo.SetMaxServerMemory inside a startup procedure, and specify the @ApplyFormula input parameter to a chosen formula number. This ensures that when the RAM on your SQL Server is upgraded, the max server memory value would be automatically increased to take advantage of the additional RAM.
Similary, if you have an SQL Server build/provisioning/deployment tool, you can incoroprate dbo.SetMaxServerMemory into your workflow to set the ‘max server memory (MB)’ value automatically.
I hope you find this post and stored procedure helpful. Feel free to leave a comment to share your thoughts.
