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.