function Get-SQLInstances

਀笀ഀഊ    $Computer = [string](hostname) -replace '(.*?)\..+','$1'

਀    ഀഊ    $Instances = $null

਀    嬀猀琀爀椀渀最嬀崀崀␀䤀渀猀琀愀渀挀攀䰀椀猀琀 㴀 ␀渀甀氀氀ഀഊ

਀    圀爀椀琀攀ⴀ嘀攀爀戀漀猀攀 ⠀∀䌀栀攀挀欀椀渀最 笀　紀∀ ⴀ昀 ␀䌀漀洀瀀甀琀攀爀⤀ഀഊ    Try 

਀    笀 ഀഊ        $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Computer) 

਀        ␀爀攀最䬀攀礀㴀 ␀爀攀最⸀伀瀀攀渀匀甀戀䬀攀礀⠀∀匀伀䘀吀圀䄀刀䔀尀尀䴀椀挀爀漀猀漀昀琀尀尀䴀椀挀爀漀猀漀昀琀 匀儀䰀 匀攀爀瘀攀爀∀⤀ഀഊ        If ($regKey.GetSubKeyNames() -contains "Instance Names") 

਀        笀ഀഊ            $regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL" ) 

਀            ␀椀渀猀琀愀渀挀攀猀 㴀 䀀⠀␀爀攀最欀攀礀⸀䜀攀琀嘀愀氀甀攀一愀洀攀猀⠀⤀⤀ഀഊ        } 

਀        䔀氀猀攀䤀昀 ⠀␀爀攀最䬀攀礀⸀䜀攀琀嘀愀氀甀攀一愀洀攀猀⠀⤀ ⴀ挀漀渀琀愀椀渀猀 ✀䤀渀猀琀愀氀氀攀搀䤀渀猀琀愀渀挀攀猀✀⤀ ഀഊ        {

਀            ␀椀猀䌀氀甀猀琀攀爀 㴀 ␀䘀愀氀猀攀ഀഊ            $instances = $regKey.GetValue('InstalledInstances')

਀        紀 ഀഊ        Else 

਀        笀ഀഊ            Continue

਀        紀ഀഊ        

਀        䤀昀 ⠀␀椀渀猀琀愀渀挀攀猀⸀挀漀甀渀琀 ⴀ最琀 　⤀ ഀഊ        { 

਀            䘀漀爀䔀愀挀栀 ⠀␀椀渀猀琀愀渀挀攀 椀渀 ␀椀渀猀琀愀渀挀攀猀⤀ ഀഊ            {

਀                ␀渀漀搀攀猀 㴀 一攀眀ⴀ伀戀樀攀挀琀 匀礀猀琀攀洀⸀䌀漀氀氀攀挀琀椀漀渀猀⸀䄀爀爀愀礀氀椀猀琀ഀഊ                $clusterName = $Null

਀                ␀椀猀䌀氀甀猀琀攀爀 㴀 ␀䘀愀氀猀攀ഀഊ                $instanceValue = $regKey.GetValue($instance)

਀                ␀椀渀猀琀愀渀挀攀刀攀最 㴀 ␀爀攀最⸀伀瀀攀渀匀甀戀䬀攀礀⠀∀匀伀䘀吀圀䄀刀䔀尀尀䴀椀挀爀漀猀漀昀琀尀尀䴀椀挀爀漀猀漀昀琀 匀儀䰀 匀攀爀瘀攀爀尀尀␀椀渀猀琀愀渀挀攀嘀愀氀甀攀∀⤀ഀഊ                If ($instanceReg.GetSubKeyNames() -contains "Cluster") 

਀                笀ഀഊ                    $isCluster = $True

਀                    ␀椀渀猀琀愀渀挀攀刀攀最䌀氀甀猀琀攀爀 㴀 ␀椀渀猀琀愀渀挀攀刀攀最⸀伀瀀攀渀匀甀戀䬀攀礀⠀✀䌀氀甀猀琀攀爀✀⤀ഀഊ                    $clusterName = $instanceRegCluster.GetValue('ClusterName')

਀                紀ഀഊ                

਀                ␀䤀渀猀琀愀渀挀攀伀戀樀攀挀琀 㴀 一攀眀ⴀ伀戀樀攀挀琀 倀匀伀戀樀攀挀琀 ⴀ倀爀漀瀀攀爀琀礀 䀀笀ഀഊ                                    Computername = $Computer

਀                                    匀儀䰀䤀渀猀琀愀渀挀攀 㴀 ␀椀渀猀琀愀渀挀攀ഀഊ                                    isCluster = $isCluster

਀                                    䌀氀甀猀琀攀爀一愀洀攀 㴀 ␀挀氀甀猀琀攀爀一愀洀攀ഀഊ                                    FullName = {

਀                                        䤀昀 ⠀␀䤀渀猀琀愀渀挀攀 ⴀ攀焀 ✀䴀匀匀儀䰀匀䔀刀嘀䔀刀✀⤀ ഀഊ                                        {

਀                                            ␀挀氀甀猀琀攀爀一愀洀攀ഀഊ                                        } 

਀                                        䔀氀猀攀䤀昀 ⠀␀䤀渀猀琀愀渀挀攀 ⴀ渀攀 ✀匀儀䰀䔀堀倀刀䔀匀匀✀⤀ഀഊ                                        {

਀                                            ∀␀挀氀甀猀琀攀爀一愀洀攀尀␀椀渀猀琀愀渀挀攀∀ഀഊ                                        }

਀                                        䔀氀猀攀 笀 䌀漀渀琀椀渀甀攀 紀ഀഊ                                    }.InvokeReturnAsIs()}

਀                  ഀഊ                $InstanceList = $InstanceList + $InstanceObject.FullName;

਀            紀ഀഊ        }

਀    紀 ഀഊ    Catch 

਀    笀 ഀഊ        Write-Warning ("{0}: {1}" -f $Computer,$_.Exception.Message)

਀    紀  ഀഊ    

਀    爀攀琀甀爀渀 ␀䤀渀猀琀愀渀挀攀䰀椀猀琀㬀ഀഊ}

਀ഀഊ#Get local machine

਀嬀猀琀爀椀渀最崀␀䌀漀洀瀀甀琀攀爀 㴀 嬀猀琀爀椀渀最崀⠀栀漀猀琀渀愀洀攀⤀ഀഊ

਀⌀䜀攀琀 琀漀琀愀氀 瀀栀礀猀椀挀愀氀 洀攀洀漀爀礀ഀഊ[int64]$total_memory_MB = (Get-WmiObject CIM_ComputerSystem).TotalPhysicalMemory / 1024 / 1024;

਀ഀഊ#Populate array of instances and their clustered status

਀␀䤀渀猀琀愀渀挀攀一愀洀攀猀 㴀 ␀渀甀氀氀ഀഊ$InstanceNames = Get-SQLInstances

਀␀䰀漀挀愀氀䤀渀猀琀愀渀挀攀猀 㴀 ␀渀甀氀氀ഀഊ$LocalInstances = @()

਀ഀഊ#Populate current host and memory values

਀嬀匀礀猀琀攀洀⸀刀攀昀氀攀挀琀椀漀渀⸀䄀猀猀攀洀戀氀礀崀㨀㨀䰀漀愀搀圀椀琀栀倀愀爀琀椀愀氀一愀洀攀⠠᠀䴀椀挀爀漀猀漀昀琀⸀匀焀氀匀攀爀瘀攀爀⸀匀䴀传ᤀ⤀ 簀 漀甀琀ⴀ渀甀氀氀ഀഊforeach ($InstanceName in $InstanceNames)

਀笀ഀഊ    $CurrentServer = $null

਀    ␀䌀甀爀爀攀渀琀匀攀爀瘀攀爀 㴀 一攀眀ⴀ伀戀樀攀挀琀 ⠀✀䴀椀挀爀漀猀漀昀琀⸀匀焀氀匀攀爀瘀攀爀⸀䴀愀渀愀最攀洀攀渀琀⸀匀䴀伀⸀匀攀爀瘀攀爀✀⤀ ∀␀䤀渀猀琀愀渀挀攀一愀洀攀∀ഀഊ    

਀    ⌀䌀栀攀挀欀 昀漀爀 氀漀挀愀氀 栀漀猀琀 漀渀氀礀ഀഊ    if ([string]::Compare($Computer,[string]($CurrentServer.ComputerNamePhysicalNETBIOS), $true) -eq 0)

਀    笀ഀഊ        $LocalInstances += $InstanceName;

਀    紀ഀഊ}

਀ഀഊ#Calculate memory values

਀嬀椀渀琀㘀㐀崀␀洀攀洀漀爀礀开瀀攀爀开椀渀猀琀愀渀挀攀开䴀䈀 㴀 ␀渀甀氀氀ഀഊ[int64]$OS_memory_buffer_MB = $null

਀ഀഊ#Calculate OS memory buffer

਀猀眀椀琀挀栀 ⴀ爀攀最攀砀 ⠀␀琀漀琀愀氀开洀攀洀漀爀礀开䴀䈀⤀ഀഊ{

਀    ∀嬀㄀ⴀ㈀崀尀搀笀㌀紀∀ 笀␀伀匀开洀攀洀漀爀礀开戀甀昀昀攀爀开䴀䈀 㴀 㔀㄀㈀㬀紀ഀഊ    "[3-9]\d{3}" {$OS_memory_buffer_MB = 1024;}

਀    ∀嬀㄀崀尀搀笀㐀紀∀ 笀␀伀匀开洀攀洀漀爀礀开戀甀昀昀攀爀开䴀䈀 㴀 ㈀　㐀㠀㬀紀ഀഊ    "[2]\d{4}" {$OS_memory_buffer_MB = 3072;}

਀    ∀嬀㌀ⴀ㐀崀尀搀笀㐀紀∀ 笀␀伀匀开洀攀洀漀爀礀开戀甀昀昀攀爀开䴀䈀 㴀 㐀　㤀㘀㬀紀ഀഊ    "[5-9]\d{4}" {$OS_memory_buffer_MB = 6144;}

਀    ∀尀搀笀㘀紀∀ 笀␀伀匀开洀攀洀漀爀礀开戀甀昀昀攀爀开䴀䈀 㴀 㠀㄀㤀㈀㬀紀ഀഊ}

਀ഀഊ#Evenly distribute memory across all instances after taking into account the OS

਀␀洀攀洀漀爀礀开瀀攀爀开椀渀猀琀愀渀挀攀开䴀䈀 㴀 ⠀␀琀漀琀愀氀开洀攀洀漀爀礀开䴀䈀 ⴀ ␀伀匀开洀攀洀漀爀礀开戀甀昀昀攀爀开䴀䈀⤀ ⼀ ␀䰀漀挀愀氀䤀渀猀琀愀渀挀攀猀⸀氀攀渀最琀栀㬀ഀഊ

਀⌀䄀氀琀攀爀 洀攀洀漀爀礀 瘀愀氀甀攀猀ഀഊforeach ($LocalInstance in $LocalInstances)

਀笀ഀഊ    $CurrentServer = $null

਀    ␀䌀甀爀爀攀渀琀匀攀爀瘀攀爀 㴀 一攀眀ⴀ伀戀樀攀挀琀 ⠀✀䴀椀挀爀漀猀漀昀琀⸀匀焀氀匀攀爀瘀攀爀⸀䴀愀渀愀最攀洀攀渀琀⸀匀䴀伀⸀匀攀爀瘀攀爀✀⤀ ∀␀䰀漀挀愀氀䤀渀猀琀愀渀挀攀∀ഀഊ    

਀    ⌀䐀漀渀✀琀 椀渀搀甀挀攀 挀栀愀渀最攀 椀昀 琀栀攀 渀攀琀 爀攀猀甀氀琀 椀猀 琀栀攀 猀愀洀攀ഀഊ    if ($CurrentServer.Configuration.MaxServerMemory.ConfigValue -ne $memory_per_instance_MB)

਀    笀ഀഊ        #Write-Host "-----------------------------------------------------------------------------" -ForegroundColor green

਀        ⌀嬀椀渀琀㘀㐀崀␀䌀甀爀爀攀渀琀䴀攀洀漀爀礀嘀愀氀甀攀 㴀 ␀䌀甀爀爀攀渀琀匀攀爀瘀攀爀⸀䌀漀渀昀椀最甀爀愀琀椀漀渀⸀䴀愀砀匀攀爀瘀攀爀䴀攀洀漀爀礀⸀䌀漀渀昀椀最嘀愀氀甀攀ഀഊ        #Write-Host "Changing $LocalInstance's maximum server memory from ($CurrentMemoryValue MB) to ($memory_per_instance_MB MB)." -ForegroundColor magenta

਀        ഀഊ        $CurrentServer.Configuration.MaxServerMemory.ConfigValue = $memory_per_instance_MB;

਀        ␀䌀甀爀爀攀渀琀匀攀爀瘀攀爀⸀䄀氀琀攀爀⠀⤀㬀ഀഊ        #Write-Host "$LocalInstance's maximum server memory is set." -ForegroundColor magenta

਀    紀ഀഊ}

਀ഀഊ