Here is a link to the official #tsql2sday invitation from Andy Yun’s Blog. This month’s subject is “Just Say No to Defaults”. If you’ve read my blog at all you will know that I have a slight obsession with SQL Server and PowerShell. As far as I am concerned PowerShell is the new gold standard when it comes to scripting and automation in the Windows environment. Add a sprinkle of SQL Server with the SQL Server PowerShell module (SQLPS) and you have a deadly combination as a DBA.
So lets start saying no to defaults! One setting I always change when I install SQL Server are the MinServerMemory and MaxServerMemory values. This blog post will demonstrate how to use PowerShell and SMO to set these values programmatically, based on the amount of RAM in the machine you are running it on.
# Load SQLPS
Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location
# Microsoft.SqlServer.Management.Smo.Server
$SqlServerName = "localhost\inst1"
$SrvObject = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $SQLServerName
# Test the connection
$SrvObject.ConnectionContext.Connect()
# Find the correct configuration property for memory
$SrvObject.Configuration | Get-Member *memory*
# Display the Min and MaxServerMemory configuration properties
$SrvObject.Configuration.MinServerMemory
$SrvObject.Configuration.MaxServerMemory
Only MaxServerMemory is shown in the output, but the MinServerMemory properties are identical. Actually, the properties for all of the SQL Server Configuration items are the same. They all contain the following 9 properties.
# Set the MaxServerMemory
$SrvObject.Configuration.MaxServerMemory.ConfigValue = 128
$SrvObject.Configuration.MaxServerMemory.ConfigValue = 512
$SrvObject.Configuration.Alter()
# Programmatically Alter the MinServerMemory and MaxServerMemory
# Load SQLPS
Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location
# Microsoft.SqlServer.Management.Smo.Server
$SqlServerName = "localhost\inst1"
$SrvObject = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $SQLServerName
# Test the connection
$SrvObject.ConnectionContext.Connect()
# Get total physical memory in the machine. There's more than 1 way to skin this cat.
$TotalRam = [int]((Get-WmiObject Win32_OperatingSystem | Select-Object -ExpandProperty TotalVisibleMemorySize) / 1024)
# Set the Max to 90% of physical memory and Min to 1/2 of Max memory.
[int]$MaxMem = $TotalRam * .9
[int]$MinMem = $MaxMem / 2
# Alter the SQL Server configuration values
$SrvObject.Configuration.MaxServerMemory.ConfigValue = $MaxMem
$SrvObject.Configuration.MinServerMemory.ConfigValue = $MinMem
$SrvObject.Configuration.Alter()
# Display the new values
$SrvObject.Configuration.MaxServerMemory
$SrvObject.Configuration.MinServerMemory
The full script displays the MinServerMemory and MaxServerMemory configuration values after the modification so you can see that your script worked as expected.
And there you have it. You can use PowerShell and SMO to set your MinServerMemory and MaxServerMemory settings based on the physical amount of RAM in your system. As a disclaimer, my calculations for MinServerMemory and MaxServerMemory is just what I used for this demo and in no way a standard for everything. I just needed a simple example to show. You could make the logic as simple or complex as you like. That part is up to you to figure out!
Just as with SQL Server itself, you can also manage SQL Server Agent configurations using PowerShell and SMO. Here is a link to that blog post for Managing SQL Server Agent Configuration with PowerShell in case you’re interested.
Enjoy and happy scripting!