functions/Set-SqlMaxMemory.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
Function Set-SqlMaxMemory
{
<#
.SYNOPSIS
Sets SQL Server max memory then displays information relating to SQL Server Max Memory configuration settings. Works on SQL Server 2000-2014.

THIS CODE IS PROVIDED "AS IS", WITH NO WARRANTIES.

Inspired by Jonathan Kehayias's post about SQL Server Max memory (http://bit.ly/sqlmemcalc), this uses a formula to determine the default optimum RAM to use, then sets the SQL max value to that number.

Jonathan notes that the formula used provides a *general recommendation* that doesn't account for everything that may be going on in your specific environment.

.PARAMETER SqlServer
Allows you to specify a comma separated list of servers to query.

.PARAMETER MaxMb
Specifies the max megabytes

.NOTES
Author : Chrissy LeMaire (@cl), netnerds.net
Requires: sysadmin access on SQL Servers

.LINK
https://dbatools.io/Set-SqlMaxMemory

.EXAMPLE
Set-SqlMaxMemory sqlserver1

Set max memory to the recommended MB on just one server, "sqlserver1"

.EXAMPLE
Set-SqlMaxMemory sqlserver1 2048

Explicitly max memory to 2048 MB on just one server, "sqlserver1"

.EXAMPLE
Get-SqlRegisteredServerName sqlserver| Get-SqlMaxMemory | Where-Object { $_.SqlMaxMB -gt $_.TotalMB } | Set-SqlMaxMemory

Find all servers in CMS that have Max SQL memory set to higher than the total memory of the server (think 2147483647), then pipe those to Set-SqlMaxMemory and use the default recommendation.

#>

    [CmdletBinding()]
    Param (
        [parameter(Position = 0)]
        [Alias("ServerInstance", "SqlInstance", "SqlServers")]
        [string[]]$SqlServer,
        [parameter(Position = 1)]
        [int]$MaxMb,
        [Parameter(ValueFromPipeline = $True)]
        [object]$collection,
        [System.Management.Automation.PSCredential]$SqlCredential
    )
    PROCESS
    {
        
        if ($SqlServer.length -eq 0 -and $collection -eq $null)
        {
            throw "You must specify a server list source using -SqlServer or you can pipe results from Get-SqlMaxMemory"
        }
        
        if ($MaxMB -eq 0)
        {
            $UseRecommended = $true
        }
        
        if ($collection -eq $null)
        {
            $collection = Get-SqlMaxMemory -SqlServer $SqlServer
        }
        
        $collection | Add-Member -NotePropertyName OldMaxValue -NotePropertyValue 0
        
        foreach ($row in $collection)
        {
            
            Write-Verbose "Attempting to connect to $sqlserver"
            try
            {
                $server = Connect-SqlServer -SqlServer $row.server -SqlCredential $SqlCredential
            }
            catch
            {
                Write-Warning "Can't connect to $sqlserver or access denied. Skipping."
                continue
            }
            
            if (!(Test-SqlSa -SqlServer $server))
            {
                Write-Error "Not a sysadmin on $servername. Skipping."
                $server.ConnectionContext.Disconnect()
                continue
            }
            
            $row.OldMaxValue = $row.SqlMaxMB
            
            try
            {
                if ($UseRecommended)
                {
                    Write-Verbose "Changing $($row.server) SQL Server max from $($row.SqlMaxMB) to $($row.RecommendedMB) MB"
                    $server.Configuration.MaxServerMemory.ConfigValue = $row.RecommendedMB
                    $row.SqlMaxMB = $row.RecommendedMB
                }
                else
                {
                    Write-Verbose "Changing $($row.server) SQL Server max from $($row.SqlMaxMB) to $MaxMB MB"
                    $server.Configuration.MaxServerMemory.ConfigValue = $MaxMB
                    $row.SqlMaxMB = $MaxMB
                }
                $server.Configuration.Alter()
                
            }
            catch { Write-Error "Could not modify Max Server Memory for $($row.server)" }
            
            $server.ConnectionContext.Disconnect()
        }
        
        return $collection | Select Server, TotalMB, OldMaxValue, @{ name = "CurrentMaxValue"; expression = { $_.SqlMaxMB } }
    }
}