SQL Server Topic - SQL Server has to know its limitations

The main objective in this blog is to draw your attention to SQL Server and the min/max memory setting. Why is this setting important? Well, SQL Server is a memory hog. It would take all the memory if you let it. SQL Server has got to know its limitations! (Movie quote from an old Clint Eastwood movie, except he wasn't referring to SQL Server of course)   Let's take a look at this setting in SQL Server and explain a little about each.

First let's log into SQL Server Management Studio. Then, from Object Explorer, right-click the server and select Properties. Click the Memory Select a page area. 

Minimum server memory

Specifies that SQL Server should start with at least the minimum amount of allocated memory and not release memory below this value. When SQL Server starts, it does not immediately allocate the value you set here. But what SQL Server does do, is stop releasing memory when it reaches that value. So, if you set that value to say 10 gigs, and it grows to 12 gigs, when SQL releases memory, it will never go below 10 gigs. In my years of experience, I have not seen come across an environment where the minimum value was changed from 0. If anyone reading this has, I would appreciate adding a comment to let us know how it has helped.  

Maximum server memory

Specifies the maximum amount of memory SQL Server can allocate when it starts and while it runs. If you have other applications running on the same machine as SQL Server, you may need to take that into account. Set the value for this option to a value that guarantees that the memory required by the other application(s) is not allocated by SQL Server.  You want to leave memory for the operating system and other applications running on the SQL Server machine. So, the maximum value should not be all the memory the machine has available.  You should also add as much physical memory as you can to the SQL server machine. 

Final thoughts

Please refer to your reseller / DBA to determine the best setting for your specific circumstances. 

According to X3 online help, https://online-help.sageerpx3.com/erp/12/staticpost/microsoft-windows-microsoft-sql-server-201620172019/?highlight=sql+memory
SQL Server memory should be at least 4 GIGS of ram, but it goes on to say it should be much more than that for production systems. I would suggest trying to keep the SQL Server machine dedicated to only what is necessary to work with Sage X3. 

If you leave the SQL Server maximum memory at the default, you are allowing it to use all your memory, if it needs it. If the operating system has no memory left to perform it's tasks, it starts paging which is a much slower activity that using RAM.  Lastly, depending on the version of SQL Server you are running, the memory values are in MB (megabytes) so be aware of the units in which you are working.