The SQL Server Misconfiguration Chronicles part 2: Instance Memory Settings


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!

Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...