# 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 https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_execution_policies?view=powershell-7.4.
# + 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://learn.microsoft.com/en-us/sql/database-engine/configure-windows/affinity64-mask-server-configuration-option?view=sql-server-ver15
# https://learn.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://blog.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.