Configuring SQL Server maximum server memory and max degree of parallelism using Powershell

As part of a puppet configuration for a SQL Server instance I am required at build to configure the maximum server memory to be 80% of the total physical memory max degree of parallelism to be half the number of available cores.

As one of the steps in the manifest to configure the SQL Server instance, I created and executed a powershell script to complete the above and then restart the services to apply the configuration.

In order to set the value of both the maximum server memory and max degree of parallelism, the total physical memory and number of cores is retrieved using WMI and then a basic calculation is returned to set the configured value. For maximum memory the configured value is rounded towards zero using the math.truncate method.

In order to run the below, their is a dependency on importing the SQLPS powershell module, steps to perform this are included at – http://msdn.microsoft.com/en-us/library/hh231286.aspx.

ScreenHunter_229 Mar. 09 08.48

Advertisements

2 thoughts on “Configuring SQL Server maximum server memory and max degree of parallelism using Powershell

  1. The script is available at https://github.com/dean1609/scripts/blob/master/Set-SQLServerConfiguration.ps1, there has been a slight change to the calculation previously used for configuring the maximum memory on the SQL Server instance.

    The current calculation will set the maximum memory value to be Total Physical Memory – 2 GB, for a SQL instance which is greater than 16GB, a further 1GB is subtracted from the Total Physical Memory for every 16GB to determine the value to set as the maximum memory for the SQL Server instance.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s