Optimal SQL MaxServerMemory Setting TSQL- All Versions (SQL 2005/SQL 2008/R2/ SQL 2012/SQL 2014CTP1)

  • [アーティクル]

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"

OptimalMaxServerMemory_New_Multi-Version.sql

Comments

  • Anonymous
    July 07, 2013
    The comment has been removed

  • Anonymous
    July 08, 2013
    Fantastic script.  Your script came out close to what I had already set, so that made me feel good, but so much easier to use.

  • Anonymous
    July 23, 2013
    Thanks Ed, Jeff for the feedback. Ed, i believe you have got a point there on the edition logic, i will get that incorporated into the script and upload again. Thanks!

  • Anonymous
    June 20, 2014
    Thank you for the script.  It has helped me set a new client today...

  • Anonymous
    August 12, 2014
    Thanks for sharing, good post ! Hmm, I think it would be better if change "@SQLVersion = 11" to "@SQLVersion in (11,12)".