Using dbatools PowerShell Module To Set MAXDOP And Max Memory

Posted on by

As I continue my blog series on using the dbatools PowerShell module, today I want to discuss using it to set MAXDOP and Max Memory on your SQL Server.  This builds directly off of my previous post on Using dbatools PowerShell Module To Configure SQL Server, so if you haven’t seen that one yet you may want to review it to get the basics.

Hard Coded Values

If you know precisely what you want your MAXDOP and Max Memory to be, you can easily hard code those values in your powershell script like this:

While hard coding our values is certainly easy to do, it’s not very reusable.  We don’t want to have to edit our script every time we run it.  Wouldn’t it be better if our script just calculated these values based off of the resources (CPU & RAM) of the server the script is running against?  Yes.  Yes it would. Dynamically calculating our values based off of the server’s RAM and CPU is still pretty easy to do, it just takes a little bit more code.

Dynamically Calculating MAXDOP

First, let’s take a look at how we might figure out what the server’s MAXDOP should be, based on the number of processors it has.  In this example, let’s say we want to use this configuration: If the server has more than 8 cores, set MAXDOP to 8; if it has 8 cores or less, set MAXDOP to half the number of cores*;

*Note: I am not saying this is the best configuration for MAXDOP, or that this is a standard calculation to always follow. I am merely providing a simple example to show how you could perform these calculations in PowerShell.

You’ll notice that in this example we loaded the SQL Server management assembly in order to find the number of cores.  There are other ways of doing this in PowerShell, and there may even be a way to do it in dbatools that I just haven’t found yet.  (If there is, please let me know!)

Dynamically Calculating Max Memory

When it comes to dynamically calculating the Max Memory on your SQL Server, you have 2 options.  You can figure out how to calculate it yourself, or you can use dbatool’s Set-DbaMaxMemory command, which uses Jonathan Kehayias’s formula to automatically determine the default optimum RAM to use, then sets the SQL Server Max Memory value to that number.  Personally, I prefer to use Jonathan’s formula, because he is way smarter than I am when it comes to this stuff.

Now, for those of you that want to actually calculate your SQL Server’s max memory based off of the amount of RAM on the server, you can still easily do that.  In the below example, we will determine how much RAM the server has, subtract 4 GB, and set SQL’s max memory to that value.  Again, this isn’t any sort of best practice, it’s just an easy calculation to use for a demo.

As in our other example, we loaded the SQL Server management assembly in order to find the amount of RAM in our SQL Server, although we could have also used Get-DbaMaxMemory to find this.

While these are very basic examples, I encourage you to expand on them and modify them to fit your needs.  The dbatools PowerShell Module offers an excellent way to create a script to standardize your SQL Server configurations, which is exactly what my team at work has started using it for.  I will be covering this in more posts with examples of how I am using dbatools to enhance my day to day SQL Server administration.

(Visited 2,424 times, 1 visits today)

5 thoughts on “Using dbatools PowerShell Module To Set MAXDOP And Max Memory”

  1. Chrissy LeMaire

    Hey Eric,
    Thank you so much for spreading the word about dbatools !

    Just a quick note that you no longer need to load up SMO classes manually, because the module does that for you on import. We even support alternative credentials with the -SqlCredential parameter. If you need to make custom connections, you can use the SMO Server wrapper command Connect-DbaInstance and pass that to -SqlInstance

  2. SQL_Ninja

    With the math above, what happens if some server person configures the server with an odd number of logical cores? In theory it shouldn’t happen, but every time I say that, people prove me wrong.

    • Eric Cobb Post author

      That’s a very good point! In testing this idea, I found the following:

      By just setting the $CoreCount variable and not declaring a type, the following result would be 2.5, as expected.
      $CoreCount = 5/2
      $CoreCount

      However, if you specify the variable type as an [int] it will automatically round the result to be 2 (although I would have expected 2.5 to round UP to 3, now DOWN to 2)
      [int]$CoreCount = 5/2
      $CoreCount

      You could also use [math]::Round($CoreCount) to round the final result.

Comments are closed.