SQLMEM
(SQL Max Memory Calculator)
This CodePlex project is a simple forms application that will allow you to easily calculate the maximum SQL Server memory according to MS best practices
By Default, SQL Server is set to use max 2TB of Ram, however I am sure that in 2013 no one has that much! This effectively means that SQL can consume all of the RAM in your server leaving nothing for the OS or other applications. This can cause performance issues. Here is how Thomas Larock, from SQL Rockstar explains it:
SQL Server (and other database systems such as Oracle and Sybase) need to read data pages into their internal memory before they can be used. Of course your server needs memory to operate as well. When your database engine and your server are competing for the same memory resources, you get bad performance. You want your server and your database engine to be like dancing partners, and less like my kids fighting over the last cupcake
There is a nice formula to define how much RAM you should
dedicate to all the SQL instances on the server, to make sure there
is enough left for the OS but… unfortunately it’s not easy!
- SQL Max Memory = TotalPhyMem – (NumOfSQLThreads * ThreadStackSize) – (1GB * CEILING(NumOfCores/4)) – OS Reserved
- NumOfSQLThreads = 256 + (NumOfProcessors*- 4) * 8 (* If NumOfProcessors > 4, else 0)
- ThreadStackSize = 2MB on x64 or 4 MB on 64-bit (IA64)
- OS Reserved = 20% of total ram for under if system has 15GB. 12.5% for over 20GB
I know, it’s not easy, and what I think it’s missing is a part really dedicated for the OS. To help you set the correct values, I developed this utility that let’s you easily calculate how much Memory you actually got available for all your SQL instances.
Exemple
Pour un serveur SQL Server 2016 de 6 CPU et 24Go de RAM allouée :
NumOfSQLThreads = 256 + (6 – 4) * 8, donc 272Mo
ThreadStackSize = On est en x64 donc 2Mo
OS Reserved = Pour 24Go de RAM, donc 3Go
On a donc :
SQL Max Memory = 24Go – 576Mo – 2Go – 3Go = 18,5G Maxi !