If you’ve ever run an installation of SQL Server, you’ll know it’s a database, and databases love, love, love memory. By design and by default, Microsoft SQL Server thinks it’s the only process on the system and is therefore given all the available memory and CPUs. As a best practice, I limit this. Here is the script I use to edit these values.

The first thing to know is how much memory/RAM the server has been allocated or has installed. SQL Server will be happy to use it all as we all know, but sadly, SQL Server isn’t the only process running. Depending on the organization, the environment, there are other processes running alongside of SQL Server. Think about AV and Backup software. Oh, did you forget about one of the most important areas of the system that needs memory available? This is the Operating System itself! Your performance will surely start to tank if your OS runs out of memory and starts to swap.

Another process that could be running on the SQL Server machine, is another instance of SQL Server! There are many reasons and implementations where multiple instances run on the same machine. It’s not nice when one instance uses all the memory and the other instances don’t get what they need!

Retrieving the Physical Memory

To keep the code clean, I’ve put this in a function. The function will return the amount of physical memory in Megabytes.

Function Get-ComputerMemory {
    $mem = Get-WMIObject -class Win32_PhysicalMemory | Measure-Object -Property Capacity -Sum 
    return ($mem.Sum / 1MB);
}

Determine SQL Server Maximum Memory

Now that we know how much memory is in the system, it’s time to make some choices on how much SQL Server will be allowed to use. These numbers have worked for me and can be found in most of my SQL Server implementations.

My calculations for how much memory to allow SQL Server to use are:

1. If the computer has less than 8GB of physical memory, allocate 80% of it to SQL Server and leave 20% for the OS and other applications
2. If the computer has more than 8GB of physical memory, reserve 2GB for the OS and other applications. SQL Server will get the remaining amount

This are my numbers that I use. And just because I’m sharing my #PowerShell code, doesn’t mean that you have to use every piece of code, character by character!

Function Get-SQLMaxMemory { 
	$memtotal = Get-ComputerMemory
    $min_os_mem = 2048 ;
    if ($memtotal -le $min_os_mem) {
        Return $null;
	}
	if ($memtotal -ge 8192) {
		$sql_mem = $memtotal - 2048
	} else {
    	$sql_mem = $memtotal * 0.8 ;
	}
    return [int]$sql_mem ;	
}

Reconfigure SQL Server Memory Allocation

This code is pretty straight forward, but SQL Server doesn’t have too many PowerShell cmdlets for us. To reconfigure the memory allocations, we have to use SQL Server Management Objects (SMO). To access SMO and pull it into our PowerShell world, we access them via .the NET Framework class. Once the class is loaded into our environment, we can then create native PowerShell objects. Pretty cool I’d say!

Function Set-SQLInstanceMemory {
	param (
		[string]$SQLInstanceName = ".", 
		[int]$maxMem = $null, 
		[int]$minMem = 0
	)

	if ($minMem -eq 0) {
		$minMem = $maxMem
	}
    [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
    $srv = New-Object Microsoft.SQLServer.Management.Smo.Server($SQLInstanceName)
	if ($srv.status) {
		Write-Host "[Running] Setting Maximum Memory to: $($srv.Configuration.MaxServerMemory.RunValue)"
		Write-Host "[Running] Setting Minimum Memory to: $($srv.Configuration.MinServerMemory.RunValue)"

		Write-Host "[New] Setting Maximum Memory to: $maxmem"
		Write-Host "[New] Setting Minimum Memory to: $minmem"
		$srv.Configuration.MaxServerMemory.ConfigValue = $maxMem
		$srv.Configuration.MinServerMemory.ConfigValue = $minMem	
		$srv.Configuration.Alter()
	}
}

** Note **

These changes take place immediately. See https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options

The min server memory and max server memory options are advanced options. If you are using the sp_configure system stored procedure to change these settings, you can change them only when show advanced options is set to 1. These settings take effect immediately without a server restart.

The previous link also has a lot of great information about memory allocation.

Making IT Work

Now that we’ve defined a whole 2 functions, we need to call them. I actually put them into one line. Looks better and cleaner in my opinion J.

$MSSQLInstance = "sql01\SQLInstance01"
Set-SQLInstanceMemory $MSSQLInstance (Get-SQLMaxMemory)

Until next time!