How to Configure SQL Server Memory Options for Best Practices

How to Configure SQL Server Memory Options for Best Practices

SQL Server is a memory intensive application. When implementing SQL Server performance best practices, one of the primary best practice is to optimize the memory options for optimal performance. SQL Server has evolved over the years and offers various capabilities which were not available before. For example, Buffer Pool Extension, Column Store Indexes, In-Memory OLTP engine for high-performance transactional systems etc. In this post, I am going to talk about how to configure SQL Server Memory Options for Best Practices.

General SQL Server Memory Best Practices

By default, SQL Server will generally try to eat up all the memory from the Operating System. This can greatly stress the Operating System from performing its core tasks. In order to prevent this, perform the following:

• For Systems with 4 GB of RAM: Reserve 1 GB of RAM for the OS,
• For Systems of 16 GB of RAM: Reserve 4 GB of RAM

You need to reserve 1 GB for the OS for every 8 GB of RAM greater than 16 GB.

For 32 GB Systems: Reserve 6 GB of RAM for the OS (4 GB till 16, then 1 GB for every 8 GB)

You need to deduct the estimated memory requirements for the Operating System and set it accordingly in the “Maximum Server Memory” setting of the SQL Server Instance properties.

For SQL Server with Higher Memory, Allocate 10% System Memory to OS

SQL Server Buffer Pool Extension Best Practices

source: msdn.microsoft.com

• Only Implement if you have High-Speed Disk I/O Subsystems (Fusion IO or SSD).
• Performs best with OLTP workloads which are read heavy.
• Not recommended for Data-warehousing or write-heavy workloads.
• Recommended for Systems with memory ranging from 8 GB – 64 GB
• Also, works in SQL Server Standard Editions

In-memory OLTP Recommendations

• Memory Requirements are outside the regular SQL Server and OS Memory Requirements.
• Size your In-memory Tables for memory size and allocate memory to system OS.
• Not recommended for Systems with Less than 64 GB of System Memory.

I hope this was useful. Just a reminder to follow the following Official Links for considerations directly from Microsoft:

• Server Memory Server Configuration Options
• Optimizing Server Performance Using Memory Configuration Options
• SQL Server Memory Options