Earlier this year I released a free T-SQL script that will calculate the correct amount of RAM you should allocate to SQL Server, assuming it is a standalone instance.
After attending the PASS Summit in Seattle in October, I visited the SQL Server Tiger team’s GitHub repository and discovered something similar, but not quite the same, in the Maintenance Solution folder.
I have taken the best ideas from their Database Server Options script and merged them into my Max Server Memory Calculator script.
New Features
The SQL Server thread stack is now taken into account. This value depends on the CPU architecture (32-bit, or x64 / IA64) and the maximum worker threads configured for the SQL Server instance.
On my 64-bit laptop with 16GB RAM, the new recommended amount for Max Server Memory has dropped from 11,264 MB to 10,112 MB (1,125 MB of RAM is now reserved for the thread stack).
Improvements
By default, the generated script will enable show advanced options before trying to set the max server memory (MB) value.
The @ProductVersion parameter uses a new method to calculate the major SQL Server version. Previously it was a hack based on the string returned by the @@VERSION function, but now it uses the @@MICROSOFTVERSION function.
This code is also from the Tiger team’s repository, and I’m sharing it here because I think it’s pretty clever how it works.
-- Get SQL Server Major Version
SELECT CONVERT(INT, (@@MICROSOFTVERSION / 0x1000000) & 0xFF);
I have also added a note on the Max Server Memory Matrix page to note that the script now accounts for the thread stack.
I hope you enjoy this new version of the script. If you have any comments or suggestions, please contact me on Twitter at @bornsql .