Today my friend John Morehouse(@SqlrUs) posted a handy T-SQL script to configure your SQL Server memory based on some rules provided by Jonathan Kehayias(@SQLPoolBoy). This is a great script and helps automate a lot of the work a DBA needs to do when setting up a new instance. Hopefully, you know by now about my passion for automating and standardization. However, when reading John’s post, I had the following internal conversation:
“Wow, self, John’s got a neat little script here.”
“Yeah, but T-SQL is so passé. Everyone does it. Plus, not sure how easily we could add that into a server deployment script if we wanted total hands-off-ness.”
“Self, I know what you mean. Nothing wrong with it, it just doesn’t fit my own particular….oh…particular….”
“Idiom?“
“Yes! Idiom! Hrmmm…how about a Powershell script?”
“A Powershell script? Brilliant!”
And so I sat down and cranked out the following script, which essentially does what John’s script does, just using Powershell.
</pre>
<#
.SYNOPSIS
Configures a SQL Server instance per the Jonathan Kehayias' guidelines.
.DESCRIPTION
This script will configure your SQL Server instance per the guidelines
found in Jonathan Kehayias' blog post: http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
The rules are:
- 1GB for initial OS reserve
- +1GB per 4GB server RAM up to 16GB
- +1GB per 8GB server RAM above 16
.PARAMETER
-instance SQL instance name, i.e. localhost\SQL2012, DBASERVER01
-apply Switch parameter, call if you want to actually apply the changes. Otherwise, a report will be produced.
.EXAMPLE
Configure-SQLMemory -instance DBASERVER01 -apply
#>
param([parameter(Mandatory=$true)][string] $instance
, [Switch] $apply
)
#load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
if($instance.Contains("\")){
$sqlhost = $instance.Split("\") | Select -First 1
}
else{
$sqlhost = $instance
}
#set memory variables
$totalmem = (gwmi Win32_ComputerSystem -computername $sqlhost).TotalPhysicalMemory/1GB
$sqlmem = [math]::floor($totalmem)
#calculate memory
while($totalmem -gt 0){
if($totalmem -gt 16){
$sqlmem -= [math]::floor(($totalmem-16)/8)
$totalmem=16
}
elseif($totalmem -gt 4){
$sqlmem -= [math]::floor(($totalmem)/4)
$totalmem = 4
}
else{
$sqlmem -= 1
$totalmem = 0
}
}
#if not in debug mode, alter config. Otherwise report current and new values.
$srv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server $instance
"Instance:" + $instance
"Max Memory:" + $srv.Configuration.MaxServerMemory.ConfigValue/1024 + " -> " + $sqlmem
"Min Memory:" + $srv.Configuration.MinServerMemory.ConfigValue/1024 + " -> " + $sqlmem/2
if($apply){
$srv.Configuration.MaxServerMemory.ConfigValue = $sqlmem * 1024
$srv.Configuration.MinServerMemory.ConfigValue = $sqlmem/2 * 1024
$srv.Configuration.Alter()
"Configuration Complete!"
}
<pre>
Now it should be noted that I’m not going to tell you which of these scripts are “better”. Neither is, really, they’re just different ways of approaching the problem. And that’s the fun of it. When working in technology, cats can be skinned in multiple ways, we just have to use something. Whether it’s T-SQL, Powershell, C#, or some other thing, the point is that you want scripts to automate your work.
What was also cool about this was it gave me another exercise to practice Powershell. After all, practice makes perfect and when learning something, any excuse to make me use it is good. John and I actually had a conversation shortly after I wrote the script about these sort of opportunities, and we’ll continue to look for ways to challenge each other for learning.
Edit: Well, that was embarrassing. Amazing how sometimes you can get the math little screwy. I’ve updated this script as of 9:44 PM MST to correct me having the math backwards. If you grabbed/reviewed this script before hand, please make sure up grab the updated version.
I’m tweeting!