12 Days of dbatools – Third Day3 min read

---

On the first day of Christmas dbatools gave to me: Find-DbaCommand

On the second day of Christmas dbatools gave to me: Get-DbaCmsRegServer

On the third day of Christmas dbatools gave to me: DbaMaxMemory

---

Command Information:

The third day is going to bring us my favorite commands.  This is a set of commands that will help you identify and standardize your SQL Server memory settings across your environment.  For this command you have a Get command which will show you the current Max Memory setting in SQL.  You also have a Test command which will run analysis against a server and give you suggestions on the amount of Max Memory to set based on the number of instances.  And finally the power house Set command which will set your Max Memory based on industry/community best practice calculations.  Using this set of commands correctly you will never have to see a SQL Server with Max Memory of 2147483647 again.

Command Description:

 Get-DbaMaxMemory

This command retrieves the SQL Server ‘Max Server Memory’ configuration setting as well as the total physical installed on the server.

Results are turned in megabytes (MB).

Set-DbaMaxMemory

Sets SQL Server max memory then displays information relating to SQL Server Max Memory configuration settings.

Inspired by Jonathan Kehayias’s post about SQL Server Max memory (http://bit.ly/sqlmemcalc), this uses a formula to
determine the default optimum RAM to use, then sets the SQL max value to that number.

Jonathan notes that the formula used provides a *general recommendation* that doesn’t account for everything that may
be going on in your specific environment.

Test-DbaMaxMemory

Inspired by Jonathan Kehayias’s post about SQL Server Max memory (http://bit.ly/sqlmemcalc), this script displays a SQL Server’s: total memory, currently configured SQL max memory, and the calculated recommendation.

Jonathan notes that the formula used provides a *general recommendation* that doesn’t account for everything that may be going on in your specific environment.

Command Example:

 Now lets run the Test command to see what is the best option for our Max Memory on our SQL Server.

So the Test shows that I have three SQL Server instances running on this computer with a total of 16GB of memory available.  Based on that the recommendation is 3 1/2 GB of Max Memory per instance.  I know that two of the other instances don’t do much so I am going to give my SQL 2017 instance 8 GB of Max Memory.

As you can see, it shows my previous Max Memory value and then the Max Value it set the memory too.  That is it, this instance is ready to go from a Max Memory standard.

Advanced Command Usage:

I don’t have an advanced command, but this Set command is a perfect example of how you could use the second day of dbatools tip Get-DbaCmsRegServer and update or check your entire environment for Max Memory settings.