It is quiet challenging to determine how much memory will be used by SQL Server, therefore infrastructure team needs to provide sufficient physical memory to the server. most of the times insufficient memory capacity can cause CPU and I/O bottlenecks, if the databases are not tuned well. You may refer to SQL Server Performance Bottlenecks blog post for more info. At this blog post, I would like to share how to calculate SQL Server estimated memory consumption based on the given formulas.
Windows Server is one of the major memory consumers in production servers, to estimate how much memory it consumes is highly depends on processor architecture and the mode of operation either with GUI or CORE. The following formulas guide you, how to estimate memory consumption.
SQL Server Dynamic Link Libraries (DLLs)
SQL Server loads many DLLs to provide full functionalities, therefore the hardware should have sufficient memory capacity to load them.
SQL Server Memory-To-Leave (MTL)
MTL is the memory section of SQL Server for worker threads, backup operations, locks and etc. This part of SQL Server memory is very important and should have sufficient space, otherwise SQL Server steals memory from Buffer Pool section to accomplish mentioned operations, which cause overall performance degrading.
SQL Server Plan Cache
SQL Server create plan cache objects for better performance (I would not go detail). Plan cache has its own memory section, this memory section is critical and should have sufficient capacity to avoid Local or Global Memory Pressure. You may refer to SQL Server Memory Pressure blog post for more information. Plan Cache memory consumption formula is differ from one version to next version.
SQL Server Buffer Pool
Buffer Pool is the memory section that keeps all data pages and needs to have sufficient memory capacity to keep data on memory as long as possible. insufficient space can cause disk I/O bottleneck due to Swap In/Out data pages. Estimating Buffer Pool size is differ by system, we will take a look at OLTP and OLAP systems.
I have made your life easier by developing a simple calculator, you may download it from SQL Server Memory Calculator Tool blog post. (More than 2000 downloads).
For setting up a stable with high performance production server, DBAs need to do some calculation before installing and configuring SQL Server to avoid future common performance bottlenecks.