sp_configure 'show advanced options', 1;  
RECONFIGURE;  
sp_configure 'max server memory', 4096;  
RECONFIGURE;  
or use PowerShell cmdlet from dbatools.io:
  
Set-DbaMaxMemory
  
explicitly set the max memory to 2048 MB on just one server, “sqlserver1”
Set-DbaMaxMemory -SqlServer sqlserver1 -MaxMb 2048 
By using sqlps module it's possible to address this task the next way:
Import-Module Sqlps
$sql16 = ls 'SQLSERVER:\SQL\(local)'|? InstanceName -eq 'SQL16'
$sql16.Configuration.MaxServerMemory.RunValue
$sql16.Configuration.MaxServerMemory.ConfigValue = [Math]::Floor($sql16.Configuration.MaxServerMemory.RunValue * 1.5)
$sql16.Alter()
$sql16.Configuration.MaxServerMemory.RunValue
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
                    
Related
                        
2511Add a column with a default value to an existing table in SQL Server
1604How to return only the Date from a SQL Server DateTime datatype
1718How to check if a column exists in a SQL Server table?
1428LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
1539Inserting multiple rows in a single SQL query?
1202How can I do an UPDATE statement with JOIN in SQL?
2373Determine installed PowerShell version
619How to run a PowerShell script
3367How do I UPDATE from a SELECT in SQL Server?
452What's the best way to determine the location of the current PowerShell script?
site design / logo © 2019 Stack Exchange Inc; user contributions licensed under cc by-sa 3.0
                            with attribution required.
                    rev 2019.5.30.33843