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:
|
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.
|
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:
|
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 |