PowerShell: Optimize SQL Server Memory & CPU Resources

# optimizeSqlServer.ps1
# Version 0.0.2
# This version deals with Memory, CPU
# Future versions will address storage optimization as well
 
$sqlServers=$env:computername
$saUser='sa'
$saPassword='PASSWORD'
$optimizeMemory=$true
$optimizeCpu=$true
$optimizeStorage=$false
 
Function optimizeSqlServer($sqlServers=$env:computername,$sa,$saPassword,$memory,$cpu,$storage){    
    function optimizeSqlResources{
        param(
            $sa,
            $saPassword,
            $memory=$true,
            $cpu=$true,
            $storage=$false
            )
        # This function performs these tasks
        # a. Detect System total RAM and CPU Cores
        # b. Calculate recommended Min and Max SQL memory usage
        # c. Apply optimal CPU/RAM configuration
        $ErrorActionPreference='stop'
        $localSqlCreds=$sa -notmatch '\\' -and $saPassword
        function confirmation($content,$testValue="I confirm",$maxAttempts=3){
                $confirmed=$false;
                $attempts=0;        
                $content|write-host
                write-host "Please review this content for accuracy.`r`n"
                while ($attempts -le $maxAttempts){
                    if($attempts++ -ge $maxAttempts){
                        write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                        break;
                        }
                    $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
                    if ($userInput.ToLower() -eq $testValue.ToLower()){
                        $confirmed=$true;
                        write-host "Confirmed!`r`n";
                        break;                
                    }elseif($userInput -like 'cancel'){
                        write-host 'Cancel command received.'
                        $confirmed=$false
                        break
                    }else{
                        cls;
                        $content|write-host
                        write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                        }
                    }
                return $confirmed;
        }
        if(!(get-command invoke-sqlcmd -ea Ignore)){
            Set-executionPolicy bypass
            # Preempt this error:
            # File C:\Program Files\WindowsPowerShell\Modules\sqlserver\21.1.18230\sqlnotebook.psm1 cannot be loaded because running scripts is disabled on this system. For
            # more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
            #     + CategoryInfo          : SecurityError: (:) [Import-Module], PSSecurityException
            #     + FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand
            [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
            if(!(get-packageprovider nuget)){
                $null=Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force
            }
            Set-PSRepository -Name (Get-PSRepository).Name -InstallationPolicy Trusted
            if(!(get-module sqlserver -ea ignore)){
                $null=Install-Module sqlserver -force
            }
            if(!(get-module sqlps -ea ignore)){
                $null=Install-Module sqlps -force
            }
            try{            
                if(get-command invoke-sqlcmd){
                    write-host "SQL Powershell Module is loaded"
                }
            }catch{
                write-warning $_
                write-host 'Unable to proceed with SQL Powershell module'
                return $false
            }
        }

        if($memory){
            $computer=Get-Ciminstance Win32_OperatingSystem
            $physicalRam=$computer.TotalVisibleMemorySize
            $byteToGbFactor=1048576 # 1024 x 1024
            $physicalRamGb=[math]::round($physicalRam/$byteToGbFactor,2)
            $osReservedRamGb=.{
                $osReserve=1
                if($physicalRamGb -le 4){
                    write-warning 'This SQL server has 4GB of RAM or less. Thus, 1GB will be reserved for the OS.'
                }
                elseif($physicalRamGb -le 16){
                    $osReserve+=(($physicalRamGb-4)/4) # 1GB added for each 4GB
                }else{
                    $osReserve+=3+(($physicalRamGb-16)/8) # 4GB of first 16GB, plus 1GB added for each 8GB
                    }
                return $osReserve
                }
            $maxSqlRamMbRecommended=($physicalRamGb-$osReservedRamGb)*1024 # convert to MB value, not GB
            $setMaxRam="
                sp_configure 'show advanced options', 1;
                GO
                RECONFIGURE;
                GO
                sp_configure 'max server memory', $maxSqlRamMbRecommended;
                GO
                RECONFIGURE;
                GO"
        
            $getCurrentRamConfig="
                SELECT m.value
                FROM sys.configurations m WHERE m.[name] = 'max server memory (MB)'
                "
            $currentMaxRamMb=if($localSqlCreds){
                    (invoke-sqlcmd -Username $sa -Password $saPassword -Query $getCurrentRamConfig -ConnectionTimeout 0).value
                    # Currently, the module only accepts SQL credentials - Domain credentials are rejected
                    # invoke-sqlcmd : Login failed for user 'domainAdmin'.
                    # At line:1 char:1
                    # + invoke-sqlcmd -username 'domainAdmin' -password $saPassword -Query  ...
                    # + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                    #     + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
                    #     + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
                }else{
                    (invoke-sqlcmd -Query $getCurrentRamConfig -ConnectionTimeout 0).value
                    }
        
            if($currentMaxRamMb -eq $maxSqlRamMbRecommended){
                write-host "Current SQL Server Memory Configuration $currentMaxRamMb setting is already optimal" -ForegroundColor Green
                $memoryFixed=$true
            }else{
                $confirmed=confirmation -content $setMaxRam
                if($confirmed){
                    try{
                        $maxQueryTimeout=[int]::MaxValue
                        if($localSqlCreds){
                            invoke-sqlcmd -username $sa -password $saPassword -Query $setMaxRam -QueryTimeout $maxQueryTimeout -ConnectionTimeout 0
                        }else{
                            invoke-sqlcmd -Query $setMaxRam -QueryTimeout $maxQueryTimeout -ConnectionTimeout 0
                            }
                        write-host "Success!"
                        $memoryFixed=$true
                    }catch{
                        Write-Error $_
                        $memoryFixed=$false
                        }
                }else{
                    write-host "Confirmations has been declined. No changes to max RAM allocation have been made."
                    $memoryFixed=$false
                    }
            }
        }else{
            $memoryFixed=$true
        }

        if($cpu){
            # Documentation:
            # https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/affinity64-mask-server-configuration-option?view=sql-server-ver15
            # https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/affinity-mask-server-configuration-option?view=sql-server-ver15
            # Use affinity mask to bind the first 32 processors, and use affinity64 mask to bind the remaining processors on the computer.

            $logicalProcessors=(Get-WmiObject win32_processor).NumberOfLogicalProcessors
            $coresCount=if($logicalProcessors -is [Array]){($logicalProcessors|measure-object -sum).sum}else{$logicalProcessors}
            $affinityMask32Recommended=.{
                # Here's the math: https://kimconnect.com/powershell-set-application-cpu-affinity-on-remote-and-local-computers/
                [int]$maxCore32bit=if($coresCount -ge 32){32}else{$coresCount}
                [int]$affinityValue=0
                [int[]]$coreNumbers=@(2..$maxCore32bit)
                $coreNumbers|%{$affinityValue += [math]::pow(2,$_-1)}
                return $affinityValue
            }
            $setCpuAffinity=if($coresCount -gt 32){
                    $affinityMask64Recommended=.{
                        [int]$affinityValue=0
                        [int[]]$coreNumbers=@(2..$coresCount)
                        $coreNumbers|%{$affinityValue += [math]::pow(2,$_-1)}
                        return $affinityValue
                    }
                    "
                    sp_configure 'show advanced options', 1;
                    RECONFIGURE;
                    GO
                    sp_configure 'affinity mask', $affinityMask32Recommended;
                    -- sp_configure 'affinity64 mask', $affinityMask64Recommended -- setting this value is unnecessary
                    RECONFIGURE;
                    GO"
                }else{                    
                    "
                    sp_configure 'show advanced options', 1;
                    RECONFIGURE;
                    GO
                    sp_configure 'affinity mask', $affinityMask32Recommended;
                    RECONFIGURE;
                    GO"
                }
            $getCurrentCpuConfig="
                SELECT c.value
                FROM sys.configurations c WHERE c.[name] = 'affinity mask'
                "
            $currentCpuConfigValue=if($localSqlCreds){
                    (invoke-sqlcmd -Username $sa -Password $saPassword -Query $getCurrentCpuConfig -ConnectionTimeout 0).value
                }else{
                    (invoke-sqlcmd -Query $getCurrentCpuConfig -ConnectionTimeout 0).value
                    }
            if($currentCpuConfigValue -eq $affinityMask32Recommended){
                write-host "Current SQL Server CPU Configuration $currentCpuConfigValue setting is already optimal" -ForegroundColor Green
                $cpuFixed=$true
            }else{
                $confirmed=confirmation -content $setCpuAffinity
                if($confirmed){
                    try{
                        $maxQueryTimeout=[int]::MaxValue
                        if($localSqlCreds){
                            invoke-sqlcmd -username $sa -password $saPassword -Query $setCpuAffinity -QueryTimeout $maxQueryTimeout -ConnectionTimeout 0
                        }else{
                            invoke-sqlcmd -Query $setCpuAffinity -QueryTimeout $maxQueryTimeout -ConnectionTimeout 0
                            }
                        write-host "Success!"
                        $cpuFixed=$true
                    }catch{
                        Write-Error $_
                        $cpuFixed=$false
                        }
                }else{
                    write-host "Confirmations has been declined. No changes to max CPU allocation have been made."
                    $cpuFixed=$false
                    }
            }    
        }else{
            $cpuFixed=$true
        }

        if($storage){
            write-host "Automatic storage optimization is currently not available in this program."
            $storageFixed=$true
        }else{
            $storageFixed=$true
        }
        return $($memoryFixed -and $cpuFixed -and $storageFixed)
    }

    $sqlServers|%{
        if($_ -match "$env:computername|$($env:COMPUTERNAME+'.'+$env:USERDNSDOMAIN)"){
            optimizeSqlResources $saUser $saPassword $memory $cpu $storage
        }else{
            invoke-command -ComputerName $_ -ScriptBlock{
                param($importFunc,$a,$b,$c,$d,$e);
                [scriptblock]::create($importFunc).invoke($a,$b,$c,$d,$e)
                } -args ${function:optimizeSqlResources},$sa,$saPassword,$memory,$cpu,$storage
            }
        }
}

optimizeSqlServer $sqlServers $saUser $saPassword $optimizeMemory $optimizeCpu $optimizeStorage
# optimizeSqlServerMemory.ps1
# Version 0.0.1
# This iteration includes inputs of sa credential
 
$sqlServers=$env:computername
$saUser='sa'
$saPassword='Password'
 
Function optimizeSqlServerMemory($sqlServers=$env:computername,$sa,$saPassword){    
    function optimizeSqlRam{
        # This function performs these tasks
        # a. Detect System total RAM
        # b. Calculate recommended Min and Max SQL memory usage
        # c. Apply optimal RAM configuration
        $ErrorActionPreference='stop'
        function confirmation($content,$testValue="I confirm",$maxAttempts=3){
                $confirmed=$false;
                $attempts=0;        
                $content|write-host
                write-host "Please review this content for accuracy.`r`n"
                while ($attempts -le $maxAttempts){
                    if($attempts++ -ge $maxAttempts){
                        write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                        break;
                        }
                    $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
                    if ($userInput.ToLower() -eq $testValue.ToLower()){
                        $confirmed=$true;
                        write-host "Confirmed!`r`n";
                        break;                
                    }elseif($userInput -like 'cancel'){
                        write-host 'Cancel command received.'
                        $confirmed=$false
                        break
                    }else{
                        cls;
                        $content|write-host
                        write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                        }
                    }
                return $confirmed;
            }
    
        try{
            import-module sqlps
        }catch{
            write--warning $_
            write-host 'Unable to proceed with SQL Powershell module'
            return $false
            }
    
        $computer=Get-Ciminstance Win32_OperatingSystem
        $physicalRam=$computer.TotalVisibleMemorySize
        $byteToGbFactor=1048576 # 1024 x 1024
        $physicalRamGb=[math]::round($physicalRam/$byteToGbFactor,2)
        $osReservedRamGb=.{
            $osReserve=1
            if($physicalRamGb -le 4){
                write-warning 'This SQL server has 4GB of RAM or less. Thus, 1GB will be reserved for the OS.'
            }
            elseif($physicalRamGb -le 16){
                $osReserve+=(($physicalRamGb-4)/4) # 1GB added for each 4GB
            }else{
                $osReserve+=3+(($physicalRamGb-16)/8) # 4GB of first 16GB, plus 1GB added for each 8GB
                }
            return $osReserve
            }
        $maxSqlRamMbRecommended=($physicalRamGb-$osReservedRamGb)*1024 # convert to MB value, not GB
    
        $setMaxRam="
            sp_configure 'show advanced options', 1;
            GO
            RECONFIGURE;
            GO
            sp_configure 'max server memory', $maxSqlRamMbRecommended;
            GO
            RECONFIGURE;
            GO"
    
        $getCurrentRamConfig="
            SELECT c.value
            FROM sys.configurations c WHERE c.[name] = 'max server memory (MB)'
            "
        $currentMaxRamMb=if($sa -notmatch '\\' -and $saPassword){
                (invoke-sqlcmd -Username $sa -Password $saPassword -Query $getCurrentRamConfig -ConnectionTimeout 0).value
                # Currently, the module only accepts SQL credentials - Domain credentials are rejected
                # invoke-sqlcmd : Login failed for user 'domainAdmin'.
                # At line:1 char:1
                # + invoke-sqlcmd -username 'domainAdmin' -password $saPassword -Query  ...
                # + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                #     + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
                #     + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
            }else{
                (invoke-sqlcmd -Query $getCurrentRamConfig -ConnectionTimeout 0).value
                }
    
        if($currentMaxRamMb -eq $maxSqlRamMbRecommended){
            write-host "Current SQL Server Memory Configuration $currentMaxRamMb setting is already optimal" -ForegroundColor Green
            return $true
        }else{
            $confirmed=confirmation -content $setMaxRam
            if($confirmed){
                try{
                    $maxQueryTimeout=[int]::MaxValue
                    if($sa -and $saPassword){
                        invoke-sqlcmd -username $sa -password $saPassword -Query $setMaxRam -QueryTimeout $maxQueryTimeout -ConnectionTimeout 0
                    }else{
                        invoke-sqlcmd -Query $setMaxRam -QueryTimeout $maxQueryTimeout -ConnectionTimeout 0
                        }
                    write-host "Success!"
                    return $true
                }catch{
                    Write-Error $_
                    return $false
                    }
            }else{
                write-host "Confirmations has been declined. No changes have been made."
                return $false
                }
        }
    }

    $sqlServers|%{
        if($_ -match "$env:computername|$($env:COMPUTERNAME+'.'+$env:USERDNSDOMAIN)"){
            optimizeSqlRam $saUser $saPassword
        }else{
            invoke-command -ComputerName $_ -ScriptBlock{param($importFunc,$x,$y); [scriptblock]::create($importFunc).invoke($x,$y)} -args ${function:optimizeSqlRam},$sa,$saPassword
            }
        }
}
 
optimizeSqlServerMemory $sqlServers $saUser $saPassword
# optimizeSqlServerMemory.ps1

$sqlServers='sql02','sql02','sql03'

Function optimizeSqlServerMemory{
    # This function performs these tasks
    # a. Detect System total RAM
    # b. Calculate recommended Min and Max SQL memory usage
    # c. Apply optimal RAM configuration

    function confirmation($content,$testValue="I confirm",$maxAttempts=3){
            $confirmed=$false;
            $attempts=0;        
            $content|write-host
            write-host "Please review this content for accuracy.`r`n"
            while ($attempts -le $maxAttempts){
                if($attempts++ -ge $maxAttempts){
                    write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                    break;
                    }
                $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
                if ($userInput.ToLower() -eq $testValue.ToLower()){
                    $confirmed=$true;
                    write-host "Confirmed!`r`n";
                    break;                
                }elseif($userInput -like 'cancel'){
                    write-host 'Cancel command received.'
                    $confirmed=$false
                    break
                }else{
                    cls;
                    $content|write-host
                    write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                    }
                }
            return $confirmed;
        }

    try{
        import-module sqlps
    }catch{
        write--warning $_
        write-host 'Unable to proceed with SQL Powershell module'
        return $false
        }

    $computer=Get-Ciminstance Win32_OperatingSystem
    $physicalRam=$computer.TotalVisibleMemorySize
    $byteToGbFactor=1048576 # 1024 x 1024
    $physicalRamGb=[math]::round($physicalRam/$byteToGbFactor,2)
    $osReservedRamGb=.{
        $osReserve=1
        if($physicalRamGb -le 4){
            write-warning 'This SQL server has 4GB of RAM or less. Thus, 1GB will be reserved for the OS.'
        }
        elseif($physicalRamGb -le 16){
            $osReserve+=(($physicalRamGb-4)/4) # 1GB added for each 4GB
        }else{
            $osReserve+=3+(($physicalRamGb-16)/8) # 4GB of first 16GB, plus 1GB added for each 8GB
            }
        return $osReserve
        }
    $maxSqlRamMbRecommended=($physicalRamGb-$osReservedRamGb)*1024 # convert to MB value, not GB

    $setMaxRam="
        sp_configure 'show advanced options', 1;
        GO
        RECONFIGURE;
        GO
        sp_configure 'max server memory', $maxSqlRamMbRecommended;
        GO
        RECONFIGURE;
        GO"

    $getCurrentRamConfig="
        SELECT c.value
        FROM sys.configurations c WHERE c.[name] = 'max server memory (MB)'
        "

    $currentMaxRamMb=(invoke-sqlcmd -Query $getCurrentRamConfig -ConnectionTimeout 0).value
    
    if($currentMaxRamMb -eq $maxSqlRamMbRecommended){
        write-host "Current SQL Server Memory Configuration $currentMaxRamMb setting is already optimal" -ForegroundColor Green
        return $true
    }else{
        $confirmed=confirmation -content $setMaxRam
        if($confirmed){
            try{
                $maxQueryTimeout=[int]::MaxValue
                invoke-sqlcmd -Query $setMaxRam -QueryTimeout $maxQueryTimeout -ConnectionTimeout 0
                write-host "Success!"
                return $true
            }catch{
                Write-Error $_
                return $false
                }
        }else{
            write-host "Confirmations has been declined. No changes have been made."
            return $false
            }
    }
}

#optimizeSqlServerMemory
$sqlServers|%{invoke-command -ComputerName $_ -ScriptBlock{param($importFunc); [scriptblock]::create($importFunc).invoke()} -args ${function:optimizeSqlServerMemory}}
# Sample Output:

#PS SQLSERVER:\> optimizeSqlServerMemory
#WARNING: The names of some imported commands from the module 'sqlps' include unapproved verbs that might make them less
# discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose
#parameter. For a list of approved verbs, type Get-Verb.
#
#        sp_configure 'show advanced options', 1;
#        GO
#        RECONFIGURE;
#        GO
#        sp_configure 'max server memory', 39936;
#        GO
#        RECONFIGURE;
#        GO
#Please review this content for accuracy.
#
#Please type in this value => I confirm <= to confirm. Input CANCEL to skip this item: i confirm
#Confirmed!
#
#VERBOSE: Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
#VERBOSE: Configuration option 'max server memory (MB)' changed from 43008 to 39936. Run the RECONFIGURE statement to
#install.

Leave a Reply

Your email address will not be published. Required fields are marked *