Could someone kindly help me to set the max memory for SQL via powershell

Question

Hi Experts,

Could someone please help me to set the max memory in SQL for the below request.

I have checked the below link. But I am not able to make out. Because I am not expertise in scripting and PowerShell. So need your help.
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver16

Answer 1

Hi，Jimmy Afflick

You can set the maximum memory through the UI prompts of SQL Server Management Studio(Make sure that the user you are using to connect to the database has the 'sysadmin' role.):

You can use sqlcmd to pass T-SQL commands to modify the maximum memory.

1.Check the sqlcmd version information.

Get-Command sqlcmd

2.Log in to the database.

Windows Authentication：

sqlcmd -S .\INSTANCENAME -E

Account Password Verification：

sqlcmd -S .\INSTANCENAME -U YourUserName -P YourPassword

3.Enable advanced options (if not already enabled)

EXEC sp_configure 'show advanced options', 1; 
RECONFIGURE;
GO

4.Set the maximum memory (in MB).

EXEC sp_configure 'max server memory', 4096;
RECONFIGURE;
GO

Answer 2

If it should be with PowerShell then use DbaTools CmdLet:

https://docs.dbatools.io/Set-DbaMaxMemory.html

Answer 3

There is not really a simple formula from the total RAM in the server to how you should set "max server memory". Well, you can set up a formula if the assumption is that there will be exactly one instance of SQL Server running on the machine, and nohting else would run on the server.

But if you have a mixed workload, that includes other SQL Server instances, Integration Services, Repotring Services etc, you need to account for these as well.

I would say that that the table you have found is not suitable for the case when you have SQL Server alone on the server, because it only gives 50% of the memory to SQL Server, and that's way to less. I would go for 85-90% in that situation.