This project is read-only.

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. 

Last edited Aug 22, 2013 at 4:41 AM by vladcatrinescu, version 3