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 to 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!

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature
If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before....
Creating maps with R and Power BI
The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you are creative and have knowledge about the...
Reading notifications
Sending monitoring alerts through Telegram
What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the...
Send mails with Azure Elastic Database Jobs
The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want similar functionality in Azure SQL...
Sorting matrices in Power BI
Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour...
The world of data is evolving
The data landscape has changed dramatically over recent years, the world of data is evolving. In the past, we mainly heard that we needed to...