The SQL Server Misconfiguration Chronicles part 2: Instance Memory Settings

In the first part of the SQL Server Misconfiguration Chronicles, we handled the database level issue of AutoClose & AutoShrink.

The second part of the misconfiguration chronicles will handle the default memory settings of a freshly installed SQL Server instance. SQL Server by default configures the instance memory settings to take a maximum memory of 2147483647MB which is about 2 Petabytes.

Why is this a bad thing?

Let’s say you have configured your SQL server memory to be able to take 2 petabytes of data, but your server only has 64GB of memory available. This will mean that SQL Server will be able to take all memory that exists on the server. But what happens to the other processes on your server? As you know you will also have to run an OS on your server, and maybe also SSIS & SSAS & god knows what. If SQL Server takes all the memory, it will be constantly fighting with the other processes to get the free memory of the server. Which will result in memory pressure and with that big CPU consumption, because data has to be swapped from memory to disk constantly. Which will then result in a bad performing server.

How do you solve this?

That answer is quite straightforward, lower the default memory setting of your SQL Server to a value which will not take all memory of your server. For example, if you have a server with 12 GB of RAM, I would start off by giving 8GB of ram to the SQL Server leaving 4GB for the OS & other processes. This will result in a better performance of your server.

Following code will allow you to alter the default MaxMemory setting of your SQL Server

Created By Stijn Wynants
SQL MaxMemoryFixer
Description: Execute Script to PRINT ALTER ServerMemory
Statements for SQL Server Instance
DECLARE @ServerMemory int
DECLARE @SM nvarchar(200)
DECLARE @CMD varchar(max)
DECLARE @MemoryForOS int
DECLARE @MemoryOtherProcesses int
SET YOUR MEMORY FOR SQL SERVER HERE !!!!
SET @ServerMemory = 14000 –> In MB
SET @MemoryForOS = 4096 –> In MB (Recommended minimum of 2048 MB Optimal 4096 MB, with more memory(64 gb,256,…) leave about 10%.
SET @MemoryOtherProcesses = 0 –> In MB (If there are no other processes just set to 0)
SET @SM = cast(@ServerMemory as nvarchar)
DECLARE @FullSystemMemory as int
SET @FullSystemMemory = (select top 1 total_physical_memory_kb/1024 from sys.dm_os_sys_memory)
IF @ServerMemory < (@FullSystemMemory – @MemoryForOS – @MemoryOtherProcesses)
Begin
SET @CMD = ‘
EXEC sp_configure ”show advanced option”, ”1”;
GO
RECONFIGURE
GO
EXEC sys.sp_configure N”max server memory (MB)”, ”’+@SM+”’
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure ”show advanced option”, ”0”;
GO
RECONFIGURE
GO
‘
END
ELSE
BEGIN
SET @CMD = ‘There is only ‘+cast((@FullSystemMemory – 2048) as varchar)+’ MB available on this server, you have tried to choose more memory then the server can deliver. Pick a lower value then ‘+@SM+’ MB’
END
PRINT @CMD

Thank you all for reading and happy reconfiguring!

The next part will be on TempDB Misconfiguration!
Stay Tuned!