I created a script sometime back for finding optimal MaxServerMemory setting using TSQL automation script, I have and some of you have used it for initial analysis. The script used to find the buffer distribution, followed by Lock Memory, SQL Cache memory, Optimizer memory etc. and based on that the MaxServermemory used to be calculated.
Now in SQL 2012 as ‘SQL Server Buffer’ is now a client of SQL Server Memory Manager, the whole calculation falls wrong as per what we achieved in the previous script. Thanks to Matt Lavery and John Balke for pointing this out and encouraging to re-write the whole logic flow again, especially as per best practices. As I got sometime this week I landed up writing the new OptimalMaxServerMemory TSQL based on the following logic:
The Script works for SQL 2014 CTP1 as well and as we move to more of T-Shirt sizing for memory distribution and management (mostly what SAP does), I believe sticking to the following 2 points would help in determining the memory configurations optimally.
MaxServerMemory Optimal (For Initial settings)=
- Reserve 1 GB of RAM for the OS,
- 1 GB for each 4 GB of RAM installed from 4–16 GB,
- 1 GB for every 8 GB RAM installed above 16 GB RAM.
- This has typically worked out well for servers that are dedicated to SQL Server.
Note: I am further working to evaluate the Logic flow as per the following (MaxServerMemory Optimal (Final)) and will incorporate that into the script soon
Though, at the mean time you all can use the following to fix the MaxServerMemory Setting. Use the first script to get going and then based on the calculation further in the Final settings, set the appropriate MaxServerMemory. Comments and feedback are welcome.
MaxServerMemory Optimal (Final)=
- “((Total system memory) – (memory for thread stack) – (OS memory requirements ~ 2-4GB) – (memory for other applications) – (memory for multipage allocations; SQLCLR, linked servers, etc)),
- Where the memory for thread stack = ((maxworker threads) *(stack size)) and the stack size is 512KB for x86 systems, 2MB for x64 systems and 4MB for IA64 systems.
- The value for ‘max worker threads’ can be found in the max_worker_count column of sys.dm_os_sys_info “
Attached is the Optimization Script: PFA: “OptimalMaxServerMemory_New_Multi-Version.sql”