Inspired by Jonathan Kehayias's post about SQL Server Max memory (http://bit.ly/sqlmemcalc), Get-SqlMaxMemory displays a SQL Server's:  total memory, currently configured SQL max memory, and the calculated SQL max memory recommendation.

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

Set-SqlMaxMemory sets SQL Server max memory then displays information relating to SQL Server Max Memory configuration settings.

Import

Import-Module .\SqlMaxMemory.psm1

To use these functions, first import the module using Import-Module.

 

Usage Examples

Get-SqlMaxMemory sqlcluster

Returns the following information for a single SQL Server "sqlcluster": Total MB on server, SQL Server's current Max MB setting, and a general SQL max memory recommendation.


Get-SqlMaxMemory -SqlCms sqlcluster

Gets the memory settings for all SQL Servers stored in the SQL Server Central Management Server "sqlcluster"

Set-SqlMaxMemory sqlcluster 25600

Sets SQL Server's Max Memory to 25600 MB on "sqlcluster"

Get-SqlMaxMemory -ServersFromFile .\sqlservers.txt | Where-Object { $_.SqlMaxMB -gt $_.TotalMB } | Set-SqlMaxMemory -UseRecommended

Gets the current memory settings for all servers within file sqlservers.txt. Next, it filters to find SQL Servers that have more memory allocated to SQL Server than exists on the server (think the default 2147483647), and then sets the SQL Max memory to the recommended amount.

Set-SqlMaxMemory -SqlCms sqlcluster -SqlCmsGroups Express -MaxMB 512 -Verbose

Sets all server SQL max memory within sqlcluster's Central Management Server's "Express" Registered Server Group to 512 MB.

 

Requirements

SQL Server Management Objects (SMO), PowerShell 3, sysadmin access on SQL Server if Set-SqlMaxMemory is used.

 

Limitations

Does not account for multiple instances on a server. While it's possible to detect if a server is running more than one instance, then doing some math, doing so accurately would require remote Registry Access, which may imapact the script's usefulness across a variety of environments.

 

PowerShell
Edit|Remove
<#  
.SYNOPSIS  
Sets SQL Server max memory then displays information relating to SQL Server Max Memory configuration settings. Works on SQL Server 2000-2014. 
 
.PARAMETER Servers 
Allows you to specify a comma separated list of servers to query. 
 
.PARAMETER ServersFromFile 
Allows you to specify a list that's been populated by a list of servers to query. The format is as follows 
server1 
server2 
server3 
 
.PARAMETER SqlCms 
Reports on a list of servers populated by the specified SQL Server Central Management Server. 
 
.PARAMETER SqlCmsGroups 
This is a parameter that appears when SqlCms has been specified. It is populated by Server Groups within the given Central Management Server. 
 
.PARAMETER MaxMB 
Specifies the max megabytes 
 
.PARAMETER UseRecommended 
Inspired by Jonathan Kehayias's post about SQL Server Max memory (http://bit.ly/sqlmemcalc), this script displays a SQL Server's:  
total memory, currently configured SQL max memory, and the calculated recommendation. 
 
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.  
 
.NOTES  
Author  : Chrissy LeMaire 
Requires:     PowerShell Version 3.0, SQL Server SMO, sysadmin access on SQL Servers 
 
.LINK  
https://gallery.technet.microsoft.com/scriptcenter/Get-Set-SQL-Max-Memory-19147057 
 
#>