SQL Server memory sizing should be planned from the beginning, in order to avoid bottlenecks later. However, in the real world, things are not so orderly. In this post, we are going to discuss the best practices for configuring SQL Server memory. Read along to know more.
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 try to eat up all the memory from the Operating System. This can significantly stress the Operating System from performing its core tasks. 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: