Some time ago, I added a free resource to this site to help DBAs know how much max server memory should be assigned to SQL Server, based on an algorithm by Jonathan Kehayias.
On 19 August 2016, I went one step further and wrote a free script to perform the calculation for your own instance, and even provided some T-SQL code to make the change as well. The only thing you have to do is run it.
Download the script from GitHub
You can visit the download page right here.
Run the script on your SQL Server instance
It’s perfectly safe to do so, as all it does is run a series of SELECT statements. Because this script intends to help you modify a server-level setting, you must have the necessary sysadmin permissions.
NOTE: You may have to enable advanced options first. This is a requirement before the Max Server Memory setting can be changed.
Here’s the script running on SQL Server 2008 R2 (click to enlarge):
And here’s the same script running on SQL Server 2016 (click to enlarge):
Copy the configuration change script from the [Script] column
-- Show Advanced Options might
-- need to be enabled first.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
-- The output from this new script
EXEC sp_configure 'max server memory (MB)', 11264;
RECONFIGURE WITH OVERRIDE;
GO
-- Turn off Show Advanced as best practice
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
GO
Make that change!
If you did it right, you’ll see something like this in the Messages pane.
Configuration option 'max server memory (MB)' changed from 2147483647 to 11264. Run the RECONFIGURE statement to install.
This should make your life easier when setting up a new server or reconfiguring a virtual machine when you need to manage the RAM allocation.
If you have any questions or comments, please find me on Twitter, at @bornsql .