Set Max Server Server Memory at Startup

Posted on by David

Max server memory is one of the SQL Server configuration options that you should change from the default value. Brent Ozar has some good advice on this here. TLDR: You should leave some extra memory for the o/s.

A good starting point is to leave 10% (4GB minimum). If you are running other processes on your SQL server instances like SSRS, SSAS or if you are running multiple instances you might need to leave a lot more.

You can easily set the max server memory to your desired value in MB using sp_configure or using SSMS and simply forget about it:

Set Max Server Memory
1
2
3

-- Replace 12345 with your value in MB 
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure 'max server memory (MB)', '12345'; RECONFIGURE;

What if you add/remove memory from your SQL instance? This is a setting that you need to remember to change. Gone are the days where you need to physically open your servers and install new DIMMS. With the advent of cloud and hypervisors - adding memory can be as easy as a few mouse clicks or running a script.

The point of this article is to consider doing this as a script at startup. If you scale up or down to a new instance type in the cloud - your max server memory could automatically be set to an appropriate value as a startup script. It’s something less to think about.

Show me the code!

This script will apply the 10%/4GB rule. You could adjust the formula as required.

Set Max Server Memory based on 10%/4GB
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

EXEC sys.sp_configure N''show advanced options'', N''1''  RECONFIGURE WITH OVERRIDE

DECLARE @MaxServerMemory INT
SELECT @MaxServerMemory=FLOOR((CASE WHEN total_physical_memory_kb <= (8*POWER(1024,2)) -- < 8GB, leave 50% for o/s
       THEN (total_physical_memory_kb * 0.5)
    WHEN total_physical_memory_kb <= (40*POWER(1024,2)) -- < 40GB, leave 4GB for o/s
       THEN total_physical_memory_kb-(4*POWER(1024,2))
       ELSE total_physical_memory_kb*0.9 --  leave 10% for o/s
       END)/1024)
FROM sys.dm_os_sys_memory

EXEC sys.sp_configure N''max server memory (MB)'', @MaxServerMemory
GO
RECONFIGURE WITH OVERRIDE
GO

EXEC sys.sp_configure N''show advanced options'', N''0''  RECONFIGURE WITH OVERRIDE

You just need to run this as a SQL Agent job at startup which you can do using the script below:

SET MAX SERVER MEMORY
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

USE [msdb]
GO

/****** Object:  Job [SET MAX SERVER MEMORY]    Script Date: 03/03/2021 22:22:46 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 03/03/2021 22:22:46 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SET MAX SERVER MEMORY',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [SET MAX SERVER MEMORY]    Script Date: 03/03/2021 22:22:46 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SET MAX SERVER MEMORY',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
EXEC sys.sp_configure N''show advanced options'', N''1''  RECONFIGURE WITH OVERRIDE

DECLARE @MaxServerMemory INT
SELECT @MaxServerMemory=FLOOR((CASE WHEN total_physical_memory_kb <= (8*POWER(1024,2)) -- < 8GB, leave 50% for o/s
       THEN (total_physical_memory_kb * 0.5)
    WHEN total_physical_memory_kb <= (40*POWER(1024,2)) -- < 40GB, leave 4GB for o/s
       THEN total_physical_memory_kb-(4*POWER(1024,2))
       ELSE total_physical_memory_kb*0.9 --  leave 10% for o/s
       END)/1024)
FROM sys.dm_os_sys_memory

EXEC sys.sp_configure N''max server memory (MB)'', @MaxServerMemory
GO
RECONFIGURE WITH OVERRIDE
GO

EXEC sys.sp_configure N''show advanced options'', N''0''  RECONFIGURE WITH OVERRIDE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'AgentStart',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20171101,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Posted in T-SQL