SQL Server Memory Capacity Planning

The estimated reading time for this post is 3 minutes

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

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.

Only applicable on Windows Server 2008 and later versions.

For X86 Architecture – GUI

Physical Memory <= 16GB, then 10%

Physical Memory >16GB and <=64GB, then 10% from 16GB and 5% from the extra.

Physical Memory >64GB, then 10% from 16GB, 5% from 48GB and 2.5% from the extra.


For X86 Architecture – CORE

Physical Memory <= 16GB, then 10%

Physical Memory >16GB and <=64GB, then 10% from 16GB and 2.5% from the extra.

Physical Memory >64GB, then 10% from 16GB, 2.5% from 48GB and 1.5% from the extra.


For X64 Architecture – GUI

Physical Memory <= 16GB, then 25%

Physical Memory >16GB and <=64GB, then 25% from 16GB and 10% from the extra.

Physical Memory >64GB, then 25% from 16GB, 10% from 48GB and 5% from the extra.


For X64 Architecture – CORE

Physical Memory <= 16GB, then 20%

Physical Memory >16GB and <=64GB, then 20% from 16GB and 7.5% from the extra.

Physical Memory >64GB, then 20% from 16GB, 7.5% from 48GB and 2.5% from the extra.


For IA64 Architecture – GUI

Physical Memory <= 16GB, then 25%

Physical Memory >16GB and <=64GB, then 25% from 16GB and 15% from the extra.

Physical Memory >64GB, then 25% from 16GB, 15% from 48GB and 7.5% from the extra.


For IA64 Architecture – CORE

Physical Memory <= 16GB, then 20%

Physical Memory >16GB and <=64GB, then 20% from 16GB and 10% from the extra.

Physical Memory >64GB, then 20% from 16GB, 10% from 48GB and 5% from the extra.

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.

For x86 Architecture: 750MB

For x64 Architecture: 1000MB

For IA64 Architecture: 1200MB

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.

For X86 Architecture

MTL = #Schedulers x 255 x 0.512MB


For X64 Architecture

MTL = #Schedulers x 255 x 2MB


For IA64 Architecture

MTL = #Schedulers x 255 x 4MB

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.

For SQL Server 2005 RTM and Service Pack 1

Physical Memory <= 8GB, then 75%

Physical Memory > 8GB and <= 64GB, then 75% from 8GB and 50% from extra.

Physical Memory > 64GB, then 75% from 8GB, 50% from 48GB and 25% from extra.


For SQL Server 2005 Service Pack 2 and Later (Up to SQL Server 2016)

Physical Memory <= 4GB, then 75%

Physical Memory > 4GB and <= 64GB, then 75% from 4GB and 10% from extra.

Physical Memory > 64GB, then 75% from 4GB, 10% from 48GB and 5% from extra.

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.

For Transactional System – OLTP

Buffer Pool = 1/6 of Active portion of database or 1/10 of entire database size (excluding empty data pages)

For Analytical Systems – OLAP (Data Warehouse)

Buffer Pool = 20% – 30% of the total database size.

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).

Conclusion

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.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz