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 @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)
SET @CMD = ‘
EXEC sp_configure ”show advanced option”, ”1”;
EXEC sys.sp_configure N”max server memory (MB)”, ”’+@SM+”’
EXEC sp_configure ”show advanced option”, ”0”;

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’

Thank you all for reading and happy reconfiguring!

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

Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...
How to easily parse JSON in Power BI Desktop
Sometimes you want to use JSON data in Power BI. And sometimes Power BI doesn’t exactly do what you want...
Power BI reports as monitoring tool
Who has not created monitoring reports or beautiful dashboards in Power BI? Would it not be great to show off...
Your Modernization Journey starts now
Recently Microsoft introduced SQL server 2022. We wrote this post to make sure you won’t be missing out on all...