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.
Deans Blog 
can you send me this script
LikeLike
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.
LikeLike